Microsoft Access Tips for Serious Users

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


Contents:

Numbering in a report

Numbering in a form

Ranking in a query

Ranking with a temporary table

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:

Control Source =1
Running Sum Over All

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
Pre-processing query sceenshot Ranking query screenshot
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:

Limitation Workaround
You cannot sort by ranking. Build yet another query using qryCustomerValueRank as an input table.
It is not easy to supply criteria to stacked queries. Have the query read the criteria from a form, e.g.
  [Forms].[Form1].[StartDate]
A report based on the query is likely to fail with:
    Multi-level group-by not allowed.
Use a DCount() expression instead of the subquery.
(This is even slower to execute.)
Results may be incorrect. If you strike this bug, force JET to materialize the subquery with:
  (SELECT  TOP 100 PERCENT  Count([CustomerID]) AS HowMany

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. 


HomeIndex of tipsTop