Microsoft Access Ranking Query Howto

by Phil Jones, January 2010

What's all this, then?

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:

  1. The name of the query in which the GetRank function is used, eg "League"
  2. The name of the field which is sorted in Descending order, eg "points"
  3. The name of the field which contains a primary key, eg "driver_id". A primary key is required. The primary key must be visible. If you have "Show" unticked the query won't work.
  4. The value of that primary key field, eg [driver_id].

The result:

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 + 1
to
If rs(sortfield) < ThisRankMeFieldValue Then Rank = Rank + 1


Did this help you? Email phil[at]pjc.me.uk. Replace [at] with @ to email.