Ranking Data 4

[brahmabull525]

I would like to create a Make Table query in Access that does the following with data in a table called tblScore.

Data in tblScore:

ID Score
100 125
200 139
300 110

Data in tblRank,

ID Score Rank
200 139 1
100 125 2
300 110 3

Any help is most appreciated! Thanks!

 

[PHV]

SELECT A.ID, A.Score, Count(*) AS Rank INTO tblRank
FROM tblScore A INNER JOIN tblScore B ON A.Score <= B.Score
GROUP BY A.ID, A.Score;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[brahmabull525]

Thanks PHV, that's exactly what I needed!

 

[dkillilea]

PHV, I've been trying to do this inside a query forever. Thank you so much. Star for you.

 

[pbibler]

PHV, this is sweet. It makes perfect sense when I think about it, but I never would have thought about it without seeing your post. Big star for you, buddy!