Pro tip: Limit the number of records in an Access group

Grouping records in Access is easy, but limiting each group to a specific number of records isn’t. Fortunately, Access is definitely up to the challenge. You could spend a lot of time writing the perfect SQL query, but the Report object offers a simpler route that users, without the benefit of specialized knowledge, can implement.

First, we’ll create a Totals query comprised of only the grouping field. Then, we’ll base a report on the Totals query. Next, we’ll create a second query that displays all the data you need to see and base a subreport on that. This second query you can limit by number of records and, in doing so, limit the grouped query. This solution is so simple that even casual Access users can implement it.

The data

You can use any simple data set in Access, but I’ll use Northwind, a sample database that tracks product inventory and sales. I’ll base a report on the Order Details table to display the two highest-priced items for each order. The Order ID value will be the grouping value. If you haven’t installed Northwind, you’ll need to do so before continuing or use your own data. Please be aware that every upgrade to Access has included changes to Northwind. Use the field and object names your version of Northwind uses, even if they’re different from my instructions. You can also expect the actual report to look different.

The grouping query

We’ll begin by basing a simple Totals query on the Order ID field in the Order Details table. This table stores each item for each order as an individual record; each order may have only one or several records. You can create this query as follows:

1. Using the method of your choice, create a query based on the Order Details table.
2. Drag Order ID from the field list to the grid.
3. In the Show/Hide group, click Totals. In Access 2003, choose Totals from the View menu. Doing so will add the GROUP BY aggregate to the grid (Figure A).
   Figure A
   
4. Choose Ascending from the Sort drop-down.
5. Save the query as qryMainGroup and close it.

The detail query

The query you just created will display a list of Order ID values. Regardless of how many items (records) each order has, the GROUP BY aggregate will display each Order ID value only once. Now, you need a query that will display the details about the products. Specifically, we’ll display the product name and price for the two highest-priced items in each order. (By items, I mean the total number of items purchased and not the price of the individual product.)

To create the detail query, do the following:

1. Using the method of your choice, create a query based on the Order Details table.
2. Add Order ID, Product ID, and Unit Price to the grid. Both queries must contain the grouping field Order ID. This is the linking field that both reports will have in common.
3. In the Criteria cell for Order ID, enter an expression that references the main report and its field. Don’t worry that the report doesn’t exist yet. Be sure to enter a complete identifier in the following form: =Reports!reportname!groupfield. In this case, enter the name Reports!TopTwoProductsMain!Order ID (Figure B). Access will enter the brackets for you. (Be sure to include the space character between Order and ID if appropriate for your version of Northwind.)
   Figure B
   
4. Next, set the query’s Top Values property to 2, which is the number of records you want in each group. Click Property Sheet in the Show/Hide group to display the query’s properties. In Access 2003, choose Properties from the View menu. Enter the value 2 (Figure C). Doing so forces the query to return only two records for each Order ID value.
   Figure C
   
5. Set the Unit Price Sort setting to Descending (Figure D). That way, the query will sort the Unit Price values from the highest to the lowest. When the query grabs the first two records, they’ll be the two highest-priced records.
   Figure D
   
6. Save the query as qrySubGroup and close it.

The report

We now have two queries. We’ll use the first, qryMainGroup, to create the main report and qrySubGroup to create the subreport. To create this report, do the following:

1. Using the method you prefer, base a simple report on the first query, qryMainGroup. Remember to name it TopTwoProductsMain (Figure E). That’s the name we used in step 3 when we built the detail query.
   Figure E
   
2. With this report in Design view, insert a subreport control into the report’s Detail section. You can use a wizard or set the properties manually as follows: Choose Query.qrySubGroup from the Source Object drop-down (Figure F).
   Figure F
   
3. Set the Link Child Fields property and the Link Master Fields property to Order ID (Figure G).
   Figure G
   
4. Save the report and view it in Report View.

As you can see in Figure H, the detailed query, which we based the subreport on, displays only two records for each order. Because we also sorted this query in descending order, those two records reflect the two highest-priced products for each order. At this point, you can continue to tweak the report’s formatting and structure.

Figure H

This report limits the number of products to two for each order.

If the report doesn’t work, or if you get an error in step 2 (while creating the report) about unbound forms or links, return to your detail query (qrySubGroup) and check the report reference in the Order ID Criteria field. That reference must match the main report and the Order ID field exactly. Remember that you must accommodate your version’s referencing rather than my instructions if your version differs.

The technique has several steps, but they’re steps that users commonly make. Plus, there’s no code — this technique requires no specialized development expertise. If you’re familiar with the interface, you can implement this Access solution for limiting the number of records in a group.

What’s the most common task that you accomplish with Access? Share your experience in the discussion thread below.

Get the latest information about Microsoft, including some tips and tricks, by automatically subscribing to TechRepublic’s Microsoft in the Enterprise newsletter.