by Phil Jones, January 2010
Here is a VBA function for Access 97 to automatically rank query results. It can be used to calculate the rank of each row in a query. Ties are worked out automatically, so you get, for example, 1, 2, 3=, 3=, 5.
Download (zipped mdb file, Access 97 format).
In the download is an example table and query. The table contains some names and numeric data field "points" which is to be ranked.
In the query, the field "points" is sorted in descending order so the driver with the most points appears first. A primary key field is required and it must be visible. For example, if "driver_id" has the Show checkbox cleared the query won't work. The magic is in the "rankme" field. It refers to a custom function "GetRank" which you will find in the VBA Modules tab. The GetRank function takes four parameters:
To rank the other way where lower is better such as golf scores and race times, change the sort in the query to Ascending order and amend this line in the VBA module:
If rs(sortfield) > ThisRankMeFieldValue Then Rank = Rank + 1to
If rs(sortfield) < ThisRankMeFieldValue Then Rank = Rank + 1
Did this help you? Email phil[at]pjc.me.uk. Replace [at] with @ to email.