This article demonstrates how you can create a report that ranks a specific
number of records for each group in order according to a top value, for
example, the top five selling products per category.
NOTE: This article explains a technique demonstrated in the sample
files, RptSampl.exe (for Microsoft Access for Windows 95 version 7.0)
and RptSmp97.exe (for Microsoft Access 97). For information about how
to obtain these sample files, please see the following articles in the
Microsoft Knowledge Base:
ACC95: Microsoft Access Sample Reports Available in Download Center
ACC97: Microsoft Access 97 Sample Reports Available in Download Center
Microsoft Access has a TopValues property that you can use in a query to
return a specific number (N) or a specified percentage (N%) of records.
Using a main/sub report with a query that has a value set for its TopValues
property, you can create a report listing the top N records for each group.
To create a top values per group report, follow these steps:
- Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).
- Create a new, blank report based on the Categories table, and open it
in Design view.
- If the field list is not displayed, on the View menu, click Field
List, and drag the CategoryId and CategoryName fields from the field
list to the report's detail section. Close and save report as
NOTE: In version 2.0, there is a space in the Category ID and Category
- Create a new query based on the Order Details and Products tables as
Type: Select Query
Join: Order Details.[ProductID] <-> Products.[ProductID]
NOTE: In version 2.0, type a space in the Product ID field.
Total: Group By
NOTE: In version 2.0, type a space in the Category ID field.
Total: Group By
NOTE: In version 2.0, there is a space in the Product Name field.
Field: SaleAmount: Sum([Order Details].[UnitPrice]*[Quantity])
NOTE: In version 2.0, type a space in the Unit Price field.
NOTE: Referencing the CategoryID from the Top_N_Per_Group report
as criteria ensures that this query filters records by CategoryID
before it applies the TopValues property to the recordset.
- Close and save the query as Top_N_Products.
- Create a new, blank report based on the Top_N_Products query.
- If it is not displayed, on the View menu, click Field List and drag
the ProductName and SaleAmount fields from the field list to the
report's detail section. Close and save the report as Top_N_Sub.
NOTE: In version 2.0, there is a space in the Product Name and Sale
- Open the Top_N_Per_Group report in Design view.
- With the report open in Design view, press the F11 key to switch to
the Database window.
- From the Database window, drag the Top_N_Sub report to the detail
section of the Top_N_Per_Group report.
- Click the Top_N_Sub report and open its property sheet.
- Delete CategoryID from the LinkChild and LinkMaster properties.
- Preview the report. Note that the Top_N_Sub subreport displays
the top five selling products for each category.
For more information about the TopValues property, search the Help Index
for "TopValues Property," or ask the Microsoft Access 97 Office Assistant.
Article ID: 132056 - Last Review: January 19, 2007 - Revision: 2.1
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
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.