Microsoft Access Tips for Serious Users

Provided by Allen Browne, February 2008.  Last updated: April 2010.

Ranking or numbering records

JET does not have features to rank/number rows as some other SQL implementations do, so this article discusses some ways to do it.

Numbering in a report

To number records in a report, use the Sorting And Grouping to sort them in the correct order. You can then show a sequence number just by adding a text box with these properties:

This is the easiest way to get a ranking, and also the most efficient to execute. However, tied results may not be what you expect. If the third and fourth entries are tied, it displays them as 3 and 4, where you might want 1, 2, 3, 3, 5.

Numbering in a form

To add a row number to the records in a form, see Stephen Lebans Row Number.

This solution has the same advantage (fast) and disadvantage (handling tied results) as the report example above.

Ranking in a query

To handle tied results correctly, count the number of records that beat the current row.

The example below works with the Northwind sample database. The first query calculates the total value of each customer's orders (ignoring the Discount field.) The second query uses that to calculate how many customers had a higher total value:

Value of all orders per customer		Ranking customers by value
SELECT Orders.CustomerID, Sum([Quantity]*[UnitPrice]) AS TotalValue FROM Orders INNER JOIN [Order Details]   ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.CustomerID;		SELECT qryCustomerValue.CustomerID, qryCustomerValue.TotalValue,   (SELECT Count([CustomerID]) AS HowMany   FROM qryCustomerValue AS Dupe   WHERE Dupe.TotalValue > qryCustomerValue.TotalValue) AS BeatenBy FROM qryCustomerValue;

The first step is a query that gives a single record for whatever you are trying to rank (customers in this case.) This is the source for the ranking query, which uses a subquery to calculate how many beat the current record.

The example above starts ranking at zero. Add 1 if you wish, i.e.
  WHERE Dupe.TotalValue > qryCustomerValue.TotalValue)  + 1 AS BeatenBy

Limitations

It can be frustrating to do anything with the ranking query:

You may need to write the query results to a temporary table so you can use them efficiently.

Ranking with a temporary table

To use a temporary table for the query above:

1. Create a table with these fields:
   

   Field Name	Data Type	Description
   CustomerID	Number (Long Integer)	Primary key
   TotalValue	Currency	the value being ranked
   BeatenBy	Number (Long Integer)	the ranking

2. Change qryCustomerValueRank into an append query:
   - In Access 2007 and 2010, click Append on the Design tab of the ribbon.
   - In earlier versions, Append is on the Query menu.
3. Since the primary key cannot be null, add criteria to qryCustomerValueRank under CustomerID:
       Is Not Null
4. Use code to populate the temporary table, and optionally open the report you based on the temporary table:

Sub cmdRank_Click()
    Dim db As DAO.Database
    Set db = CurrentDb()
    db.Execute "DELETE FROM MyTempTable;", dbFailOnError
    db.Execute "qryCustomerValueRank", dbFailOnError
    DoCmd.OpenReport "Report1", acViewPreview
    Set db = Nothing
End Sub

Using an AutoNumber instead

If you are not concerned with how ties are handled, you could avoid the ranking query and just use an AutoNumber in your temp table to number the rows:

1. Add an AutoNumber column to the temporary table.
2. Change qryCustomerValue so it sorts Descending on the TotalValue.
3. Change it into an Append query.

To reset the AutoNumber after clearing out the temporary table, compact the database. Alternatively, reset the seed programmatically.