ACC: How to Rank Records Within a Query

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article has three examples that show you how to rank records within a query. The first example ranks records from a table. The second example ranks the results of a totals query. The third example demonstrates an alternative method of handling ties in ranking.

All the examples in this article require a field containing a value that can be evaluated using either the GREATER THAN (>) or the LESS THAN (<) operator, and a subquery to count the number of records that are greater than or less than the current record.

NOTE: A demonstration of the technique used in this article can be seen in the sample file, Qrysmp97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

Q182568 ACC97: Microsoft Access 97 Sample Queries Available on MSL

MORE INFORMATION

In Example 1 and Example 2 below, records following a tie in ranking are given the same rank number they would have received had there been no tie. The following table demonstrates a sample result with a tie in ranking:

   Salesperson   Sales   Rank
   --------------------------
   Peterson      $8000   1
   Wakita        $7000   2
   Akerley       $7000   2
   Reston        $6000   4 

   Salesperson   Sales   Rank
   --------------------------
   Peterson      $8000   1
   Wakita        $7000   2
   Akerley       $7000   2
   Reston        $6000   3 

Example 1

1. Open the sample Northwind.mdb (or NWIND.MDB in version 2.0) and create a new query based on the Employees table.



2. If the property sheet is not displayed, click the Properties button on the toolbar.



3. Click the title bar of the Employees field list, and then set the field list's Alias property to Emp1.



4. Drag the LastName and HireDate fields from the field list to the query grid.
   
   NOTE: In version 2.0, there is a space in the Last Name and the Hire Date field names.



5. In the HireDate column's Sort row, select Ascending.



6. In the third column's Field row, enter the following expression.
   
   NOTE: In the following expressions, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating these expressions.
   

   
         Seniority: (Select Count(*) from Employees Where _
         [HireDate] < [Emp1].[HireDate];) 




7. Run the query. Note that the returned records are ranked sequentially starting at 0. The subquery returns the number of employees hired before the current employee. To rank the records starting with 1, use the following expression in step 6 above:
   

   
         Seniority: (Select Count(*) from Employees Where _
         [HireDate] < [Emp1].[HireDate]+1;) 




8. To see the results of a tie in ranking, change the hire date of any employee to match another (in the Employees table).

Example 2

1. Create a new query based on the Products table.



2. Drag the CategoryID and ProductID fields from the field list to the query grid.
   
   NOTE: In version 2.0, there is a space in the Category ID and the Product ID field names.



3. Click the Totals button on the toolbar.



4. In the CategoryID column's Total row, select Group By. In the ProductID column's Total row, select Count. Save the query as ProductCount.



5. Create a new query based on the ProductCount query.



6. Set the ProductCount field list's Alias property to Prod1.



7. Drag both fields from the field list to the query grid, and then select Descending in the CountofProductID column's Sort row.



8. In the third column's Field row, enter the following expression.
   
   NOTE: In the following expression, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this expression.
   

   
         Ranking: (Select Count(*) from ProductCount Where _
         [CountofProductID] > [Prod1].[CountofProductID]) + 1 




9. Run the query.

Example 3

1. Repeat steps 1 through 8 in example 2. After step 8, save the query as CatRanking. Run the query and note the tie in the number of products between the Seafood, Condiments, and Beverages categories, CategoryIDs 8, 2, and 1, respectively.
   
   NOTE: In version 2.0, there is a space in the Category ID field name.



2. Create a new query based on the Products table. Drag the CategoryID and ProductID fields from the field list to the query grid.
   
   NOTE: In version 2.0, there is a space in the Product ID field name.



3. Click the Totals button on the toolbar.



4. In the CategoryID column's Total row, select Group By. In the ProductID column's Total row, select Count.



5. In the CategoryID column's Show row, clear the check box.



6. Set the query's UniqueValues property to Yes.



7. Run the query. Note that the query returns a list of the eight different totals of orders placed. Save the query as DistinctCount.



8. View the CatRanking query in Design view. Replace the expression in the third column's Field row with the following expression, and then run the query.
   
   NOTE: In the following expression, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this expression.
   

   
         Ranking: (Select Count(*) from DistinctCount Where _
         [CountofProductID] > Prod1.[CountofProductID]) + 1 

Additional query words: top first

Keywords          : QryHowto 
Version           : 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: April 7, 1999