A query to rank or number the results is often requested. In more powerful database management systems such as Microsoft SQL and DB2, there are often functions to do this. However, in Microsoft Access, no such function exists.
Options
In Access, there are several workarounds:
- A running sum in a report
- VBA code called in the SELECT clause
- A subquery in the SELECT clause
- A DCount in the SELECT clause
- Joining the table to itself and using a COUNT
Option 1 is probably the easiest if you want to number the rows consecutively but is the least flexible of the options.
Options 2, 3, and 4 require each row to be evaluated separately and can be slow for large data sets.
Option 5 is the most complicated to understand but can often be the most efficient. That is the option I will be discussing in this article.
Examples
Given the following table and data:
Expand|Select|Wrap|Line Numbers
- ID Salesperson Division NumberSold
- 1 Robert Electronics 99
- 2 Jenny Electronics 54
- 3 Billy Appliances 54
- 4 Karen Appliances 102
- 5 Kim Appliances 30
Query
Expand|Select|Wrap|Line Numbers
- SELECT
- t1.Salesperson,
- t1.Division,
- t1.NumberSold,
- COUNT(*) + 1 AS Rank
- FROM
- tblSales AS t1
- LEFT JOIN tblSales AS t2
- ON t1.NumberSold < t2.NumberSold
- GROUP BY
- t1.Salesperson,
- t1.Division,
- t1.NumberSold
Expand|Select|Wrap|Line Numbers
- Salesperson Division NumberSold Rank
- Robert Electronics 99 2
- Jenny Electronics 54 3
- Billy Appliances 54 3
- Karen Appliances 102 1
- Kim Appliances 30 5
Query
Expand|Select|Wrap|Line Numbers
- SELECT
- t1.Salesperson,
- t1.Division,
- t1.NumberSold,
- COUNT(*) AS Rank
- FROM
- tblSales AS t1
- LEFT JOIN tblSales AS t2
- ON t1.NumberSold < t2.NumberSold OR
- (t1.NumberSold = t2.NumberSold AND
- t1.ID <= t2.ID)
- GROUP BY
- t1.Salesperson,
- t1.Division,
- t1.NumberSold
Expand|Select|Wrap|Line Numbers
- Salesperson Division NumberSold Rank
- Robert Electronics 99 2
- Jenny Electronics 54 4
- Billy Appliances 54 3
- Karen Appliances 102 1
- Kim Appliances 30 5
Query
Expand|Select|Wrap|Line Numbers
- SELECT
- t1.Salesperson,
- t1.Division,
- t1.NumberSold,
- COUNT(*) AS Rank
- FROM
- tblSales AS t1
- LEFT JOIN tblSales AS t2
- ON t1.Division = t2.Division AND
- t1.NumberSold <= t2.NumberSold
- GROUP BY
- t1.Salesperson,
- t1.Division,
- t1.NumberSold
Expand|Select|Wrap|Line Numbers
- Salesperson Division NumberSold Rank
- Robert Electronics 99 1
- Jenny Electronics 54 2
- Billy Appliances 54 2
- Karen Appliances 102 1
- Kim Appliances 30 3