You are currently offline, waiting for your internet to reconnect

ACC2000: How to Rank Records Within a Query

This article was previously published under Q208946
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

All the examples in this article require a field that has a value thatcan 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: You can see a demonstration of the technique that is used in this article in the sample file Qrysmp00.exe. For information about how to obtain this sample file, please see the following article in the MicrosoftKnowledge Base:
207626 ACC2000: Access 2000 Sample Queries Available in Download Center
MORE INFORMATION
In Example 1 and Example 2 below, records following a tie in ranking aregiven 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:
SalespersonSalesRank
Peterson$80001
Wakita$70002
Akerley$70002
Reston$60004
In Example 3 below, a second query, which has a DISTINCT clause, is used toreturn a unique list of ranking less than that of the current record. Thefollowing table, using the same records as the table above, demonstratesthe results of this method:
SalespersonSalesRank
Peterson$80001
Wakita$70002
Akerley$70002
Reston$60003

Example 1

This example returns results that show the order in which employees werehired.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Open the sample Northwind.mdb and create a new query based on the Employees table.
  2. If the property sheet is not displayed, click Properties button on the toolbar.
  3. Click the title bar of the Employees field list, and then set thefield list's Alias property to Emp1.
  4. Drag the LastName and HireDate fields from the field list to thequery grid.
  5. In the Sort row of the HireDate column, select Ascending.
  6. In the Field row of the third column, enter the following expression:
       Seniority: (Select Count(*) from Employees Where [HireDate] < _   [Emp1].[HireDate];)
  7. Run the query. Note that the returned records are ranked sequentiallystarting at 0. The subquery returns the number of employees hired beforethe current employee. To rank the records starting with 1, use thefollowing 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 anyemployee to match another (in the Employees table).

Example 2

This example returns results that rank categories by the number of productsin each category.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Create a new query based on the Products table.
  2. Drag the CategoryID and ProductID fields from the field list to thequery grid.
  3. Click the Totals button on the toolbar.
  4. In the Total row of the CategoryID column, select Group By. In the Total row of the ProductID column, 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 thenselect Descending in the Sort row of the CountofProductID column.
  8. In the Field row of the third column, enter the following expression.
       Ranking: (Select Count(*) from ProductCount Where [CountofProductID] > _   [Prod1].[CountofProductID]) + 1
  9. Run the query.

Example 3

This example demonstrates an alternative method of handling ties inranking.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  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.
  2. Create a new query based on the Products table. Drag the CategoryIDand ProductID fields from the field list to the query grid.
  3. Click the Totals button on the toolbar.
  4. In the Total row of the CategoryID column, select Group By. In the Total row of the ProductID column, select Count.
  5. In the Show row of the CategoryID column, clear the check box.
  6. Set the UniqueValues property of the query to Yes.
  7. Run the query. Note that the query returns a list of the six different totals of orders placed. Save the query as DistinctCount.
  8. View the CatRanking query in Design view. Replace the expressionin the Field row of the third column with the following expression, andthen run the query.
       Ranking: (Select Count(*) from DistinctCount Where _    [CountofProductID] > Prod1.[CountofProductID]) + 1
    Note that the records following a tie in ranking are given the same rank number they would have received had there been no tie.
top first Qrysmp00 exe
Properties

Article ID: 208946 - Last Review: 12/05/2015 10:59:14 - Revision: 1.0

  • Microsoft Access 2000 Standard Edition
  • kbnosurvey kbarchive kbinfo KB208946
Feedback
  • © 2015 Microsoft