By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Query (or VBA) to sort data by category

P: 25
Hello!

I have data that consist of the follow as an example: Team name, player name and the score for that player. in the same time i have a total for that team in the dataset so it looks like this:

team player score

red John 5
red Mike 7
red Josh 4
red total 16
green Ron 4
green Ryan 7
green Eddie 6
green total 17

so if that is my dataset, i would like to sort it by team's total score, however keeping all the players with that team. As a result i would have all the players listed from team with most points on top and then the total for that team, following by the 2nd team's players and then 2nd team total, and so on...
Any Ideas?? I really appreciate any suggestions, Thanks.
Jul 27 '07 #1
Share this Question
Share on Google+
2 Replies


missinglinq
Expert 2.5K+
P: 3,532
You have inadvertently posted your question in the Articles section rather than the Forum section. I have moved it to the Forum for you.

Linq ;0)>
Jul 27 '07 #2

Expert 100+
P: 296
Hello!

I have data that consist of the follow as an example: Team name, player name and the score for that player. in the same time i have a total for that team in the dataset so it looks like this:

team player score

red John 5
red Mike 7
red Josh 4
red total 16
green Ron 4
green Ryan 7
green Eddie 6
green total 17

so if that is my dataset, i would like to sort it by team's total score, however keeping all the players with that team. As a result i would have all the players listed from team with most points on top and then the total for that team, following by the 2nd team's players and then 2nd team total, and so on...
Any Ideas?? I really appreciate any suggestions, Thanks.
If you put this line in your SQL:
ORDER BY [TeamName], [Score]
And in design view of your query, under [Score], change the sort from Ascending to Descending.

That should give you a result like:

team player score

green total 17
green Ryan 7
green Eddie 6
green Ron 4
red total 16
red Mike 7
red John 5
red Josh 4

I'm not sure exactly how you would get the total to show up at the end of the list. I'm a little confused actually why the total would be stored in a table because it is a calculated value. It would be better if you calculated it in your query, which would create a separate column for it, or on your report.

If you create the calculated value in your query rather than store it in a table, you can use
ORDER BY [TotalScore],[Team]
where [TotalScore] is sorted in Descending order and it should give you a result like this:


team player score total

green Ryan 7 17
green Eddie 6 17
green Ron 4 17
red Mike 7 16
red John 5 16
red Josh 4 16
Jul 27 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.