Provided by Allen Browne, February 2008. Last updated: April 2010.
JET does not have features to rank/number rows as some other SQL implementations do, so this article discusses some ways to do it.
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:
|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.
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.
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|
Sum([Quantity]*[UnitPrice]) AS TotalValue
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.CustomerID;
(SELECT Count([CustomerID]) AS HowMany
FROM qryCustomerValue AS Dupe
WHERE Dupe.TotalValue > qryCustomerValue.TotalValue)
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
It can be frustrating to do anything with the ranking query:
|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.
|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.
To use a temporary table for the query above:
|Field Name||Data Type||Description|
|CustomerID||Number (Long Integer)||Primary key|
|TotalValue||Currency||the value being ranked|
|BeatenBy||Number (Long Integer)||the ranking|
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:
To reset the AutoNumber after clearing out the temporary table, compact the database. Alternatively, reset the seed programmatically.
|Home||Index of tips||Top|