You are currently offline, waiting for your internet to reconnect

ACC: How to Create a Top N Values per Group Query

This article was previously published under Q153747
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY
This article shows you two methods that you can use to create queries thatlist only the top N items per group. By using either one of these methods,you can create a query, for example, that displays only the top five salespeople for each region.

The second method described in this article assumes that you are familiarwith Visual Basic for Applications and with creating Microsoft Accessapplications using the programming tools provided with Microsoft Access.For more information about Visual Basic for Applications, please refer toyour version of the "Building Applications with Microsoft Access" manual.
MORE INFORMATION
In order to list only the top N items within a group in a query, you mustspecify a criteria that dynamically reads the grouping column in the queryand limits the item column to the top N values within each group. Method 1uses a SQL subquery to dynamically generate a list of the top N itemsfor each group, and then uses this list as the criteria for the item columnusing the IN operator. Method 2 uses a user-defined function to return the Nth item within aspecific group, which is then used with the >= operator to return the Nthand greater items.

Method 1

The following example shows you how to create a query in the Northwindsample database that displays the top three UnitsInStock per CategoryID.The query uses a SQL subquery, which returns the top three UnitsInStockgiven a specific CategoryID, and then uses the IN operator to limit therecords in the main query.

NOTE: In the criteria example in Step 5, an underscore (_) at the end of aline is used as a line-continuation character. Remove the underscorefrom the end of the line when re-creating the criteria.

  1. Open the sample database Northwind.mdb.
  2. Click the Queries tab, and then click New.
  3. Click Design View, and then click OK.
  4. In the Show Table dialog box, add the Categories and the Products tables, and then click Close.
  5. Add the following fields to the query grid:
    Field: CategoryName
    Sort: Ascending

    Field: ProductName

    Field: UnitsInStock
    Sort: Descending
    Criteria: In (Select Top 3 [UnitsInStock] From Products Where _
    [CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock] Desc)
  6. Run the query. Note that the query returns the top three UnitsInStock for each category.

Method 2

The following example demonstrates how to create a query in the Northwindsample database that displays the last five orders placed per customer.The query uses a criteria function called NthInGroup() that returns thefifth most recent OrderDate given a specific CustomerID. Using the >=operator along with this function returns all orders that were orderedon or after the fifth most recent OrderDate for each customer:

  1. Open the sample database Northwind.mdb.
  2. Create a new module and add the following line to the Declarations section if it is not already there:
    Option Explicit
  3. Create the following procedure:
          Function NthInGroup(GroupID, N)      ' Returns the Nth Item in GroupID for use as a Top N per group      ' query criteria.      Static LastGroupId, LastNthInGroup      Dim ItemName, GroupIDName, GDC, SearchTable      Dim SQL As String, rs As Recordset, db As DATABASE      If (LastGroupId = GroupID) Then         ' Returned saved result if function is called with the         ' same GroupID more than once in a row.         NthInGroup = LastNthInGroup      Else         ' Set the SQL statement parameters. These are the only items         ' that need to be customized in this function.         ' Set to Item field name.         ItemName = "OrderDate"         ' Set to Group ID field name.         GroupIDName = "CustomerID"         ' GroupID Delimiter Character:         ' For Text use "'" (Note that this is a quotation mark, a space,         ' an apostrophe, a space, and then a quotation mark. The spaces         ' are necessary for SQL statements), Date "#", Numeric ""         GDC = "'"         ' Set to search table.         SearchTable = "Orders"         ' Build a Top N SQL statement dynamically given N and         ' GroupID as parameters. Note that the sort is by the         ' item in descending order, in order to get the Top N         ' largest items.         SQL = "Select Top " & N & " [" & ItemName & "] "         SQL = SQL & "From [" & SearchTable & "] "         SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID & GDC _            & " "         SQL = SQL & "Order By [" & ItemName & "] Desc"         ' Open up recordset on Top N SQL statement and read the         ' last record to get the smallest item in the Top N.         Set db = CurrentDb()         Set rs = db.OpenRecordset(SQL)         If (rs.BOF And rs.EOF) Then            ' No matches found, return a null.            LastNthInGroup = Null            LastGroupId = GroupID            NthInGroup = LastNthInGroup            Else               ' Return the smallest Top N item in the group.               rs.MoveLast               LastNthInGroup = rs(ItemName)               LastGroupId = GroupID               NthInGroup = LastNthInGroup             End If          End If       End Function						
  4. Compile the module, and then close and save the module as basTopN.
  5. Click the Queries tab, and then click New.
  6. Click Design View, and then click OK.
  7. In the Show Table dialog box, add the Customers and the Orders tables. Click Close.
  8. Add the following fields to the query grid:
    Field: CustomerID
    Sort: Ascending


    Field: OrderID

    Field: Order Date
    Sort: Descending
    Criteria: >= NthInGroup([Customers].[CustomerID],5)
  9. Run the query. Note that for all customers who have at least five orders, the query returns the five most recent orders. For customers with fewer than five orders, the query returns all orders.
REFERENCES
For more information about subqueries, search for subqueries, and thenSQL subqueries using the Microsoft Access 97 Help Index.
Properties

Article ID: 153747 - Last Review: 01/19/2007 17:42:46 - Revision: 3.3

  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
  • kbhowto kbprogramming KB153747
Feedback