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
25
