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

ID: Q153747

The information in this article applies to:

• Microsoft Access versions 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article shows you two methods that you can use to create queries that list only the top N items per group. By using either one of these methods, you can create a query that displays only the top five salespeople for each region.

The second method described in this article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your 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 must specify a criteria that dynamically reads the grouping column in the query and limits the item column to the top N values within each group. Method 1 uses a SQL subquery to dynamically generate a list of the top N items for each group, and then uses this list as the criteria for the item column using the IN operator.

Method 2 uses a user-defined function to return the Nth item within a specific group, which is then used with the >= operator to return the Nth and greater items.

Method 1

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

NOTE: In the criteria example in Step 5, 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 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.

      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)

   for each category.

Method 2

The following example demonstrates how to create a query in the Northwind sample database that displays the last five orders placed per customer. The query uses a criteria function called NthInGroup() that returns the fifth most recent OrderDate given a specific CustomerID. Using the >= operator along with this function returns all orders that were ordered on 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

      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

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.

      Field: CustomerID
      Sort: Ascending

      Field: OrderID

      Field: Order Date
      Sort: Descending
      Criteria: >= NthInGroup([Customers].[CustomerID],5)

   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 then "SQL subqueries" using the Microsoft Access 97 Help Index.

Keywords          : kbdta
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 20, 1998