Ranking Queries in MS Access SQL

Introduction
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:

1. A running sum in a report
2. VBA code called in the SELECT clause
3. A subquery in the SELECT clause
4. A DCount in the SELECT clause
5. 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

1. ID Salesperson Division    NumberSold
2. 1  Robert      Electronics 99
3. 2  Jenny       Electronics 54
4. 3  Billy       Appliances  54
5. 4  Karen       Appliances  102
6. 5  Kim         Appliances  30

For the first example, let's say you want to rank all the salespeople by number of items sold, you can join the table to itself on the number sold and do a count.

Query

Expand|Select|Wrap|Line Numbers

1. SELECT 
2.    t1.Salesperson,
3.    t1.Division,
4.    t1.NumberSold,
5.    COUNT(*) + 1 AS Rank
6. FROM
7.    tblSales AS t1
8.  
9.    LEFT JOIN tblSales AS t2
10.       ON t1.NumberSold < t2.NumberSold
11. GROUP BY
12.    t1.Salesperson,
13.    t1.Division,
14.    t1.NumberSold

Results

Expand|Select|Wrap|Line Numbers

1. Salesperson Division    NumberSold Rank
2. Robert      Electronics 99         2
3. Jenny       Electronics 54         3
4. Billy       Appliances  54         3
5. Karen       Appliances  102        1
6. Kim         Appliances  30         5

Note that this gives ties the same rank. If what you want is to number the rows rather than rank them, you will need to use a unique field.

Query

Expand|Select|Wrap|Line Numbers

1. SELECT 
2.    t1.Salesperson,
3.    t1.Division,
4.    t1.NumberSold,
5.    COUNT(*) AS Rank
6. FROM
7.    tblSales AS t1
8.  
9.    LEFT JOIN tblSales AS t2
10.       ON t1.NumberSold < t2.NumberSold OR
11.          (t1.NumberSold = t2.NumberSold AND
12.          t1.ID <= t2.ID)
13. GROUP BY
14.    t1.Salesperson,
15.    t1.Division,
16.    t1.NumberSold

Results

Expand|Select|Wrap|Line Numbers

1. Salesperson Division    NumberSold Rank
2. Robert      Electronics 99         2
3. Jenny       Electronics 54         4
4. Billy       Appliances  54         3
5. Karen       Appliances  102        1
6. Kim         Appliances  30         5

If you want to break out the rankings or numbering by grouping field(s), you can do that by including them in the JOIN clause.

Query

Expand|Select|Wrap|Line Numbers

1. SELECT 
2.    t1.Salesperson,
3.    t1.Division,
4.    t1.NumberSold,
5.    COUNT(*) AS Rank
6. FROM
7.    tblSales AS t1
8.  
9.    LEFT JOIN tblSales AS t2
10.       ON t1.Division = t2.Division AND
11.          t1.NumberSold <= t2.NumberSold
12. GROUP BY
13.    t1.Salesperson,
14.    t1.Division,
15.    t1.NumberSold

Results

Expand|Select|Wrap|Line Numbers

1. Salesperson Division    NumberSold Rank
2. Robert      Electronics 99         1
3. Jenny       Electronics 54         2
4. Billy       Appliances  54         2
5. Karen       Appliances  102        1
6. Kim         Appliances  30         3

Note that this ranks from highest to lowest. Going from lowest to highest merely requires flipping the less than operator to a greater than operator. 

sumber : https://bytes.com/topic/access/insights/954764-ranking-queries-ms-access-sql