Unanswered: rank records in access sql (row number)
I want to have a column which shows the rank in a column based on an existing column.
ex: col 2 is a number column having values 1..10
I want a query which will rank these records based on their descending order..i.e record having col2 value = 10 will have rank 1, value=9 will have rank 2 etc...
if 2 column have the same value, they will have the same rank.
You'd use RANK() or DENSE_RANK() in SQL Server for this. These work differently, so you'd also need to explain what behaviour you would want for your purposes.
There is no equivalent in Access I'm afraid. I suspect a version of RANK() or DENSE_RANK() will be an ugly and innefficient VBA function. If there are only a handful of rows it might not be too bad though.
You can do a similar thing in Access using a query.
SELECT final_rankings.tipper_name, final_rankings.total, (select count(*) from test_rankings where [total]>[final_rankings]![total]+1 AS Rankings
FROM test_rankings AS final_rankings
ORDER BY final_rankings.total DESC;
test_rankings is the name of the table
final_rankings is the table's alias in the query.
final_rankings.total is the total field
This will not display an = sign when two rankings are the same