Rank ordering records in dynaset

[bradyj]

Here is an Access question for you.

Say I have a table of people’s weights and their names:

Joe 150
Pete 160
Dave 165

I can make a query than sorts in descending order:

Dave 165
Pete 160
Joe 150

No problem. But what if I want to assign a score based on weight. I.e. the highest weight gets the highest score. So, my output would be:

Dave 165 1
Pete 160 2
Joe 150 3

How do I get the scores in a calculated field? Is that possible in Access?

[NeoPa]

You can do it with subqueries (Subqueries in SQL) :

SELECT Name,
       Score,
       (SELECT Count(Name)
        FROM tblTest3 AS sT3
        WHERE sT3.Score>tT3.Score)+1 AS Pos
 
FROM tblTest3 AS tT3