ACC: How to Create a Top Values Per Group Report

MORE INFORMATION

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:

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).
2. Create a new, blank report based on the Categories table, and open it in Design view.
3. 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 Top_N_Per_Group.
   
   NOTE: In version 2.0, there is a space in the Category ID and Category Name fields.
4. Create a new query based on the Order Details and Products tables as follows:
   

          Query: Top_N_Products
          ----------------------------------------------------------
   
             Type: Select Query
             Join: Order Details.[ProductID] <-> Products.[ProductID]
             TopValues: 5
   
             NOTE: In version 2.0, type a space in the Product ID field.
   
             Field: CategoryID
             Table: Products
             Total: Group By
             Criteria: [Reports]![Top_N_Per_Group]![CategoryID]
   
             NOTE: In version 2.0, type a space in the Category ID field.
   
             Field: ProductName
             Table: Products
             Total: Group By
   
             NOTE: In version 2.0, there is a space in the Product Name field.
   
             Field: SaleAmount: Sum([Order Details].[UnitPrice]*[Quantity])
             Total: Expression
             Sort: Descending
   
             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.
   						

5. Close and save the query as Top_N_Products.
6. Create a new, blank report based on the Top_N_Products query.
7. 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 Amount fields.
8. Open the Top_N_Per_Group report in Design view.
9. With the report open in Design view, press the F11 key to switch to the Database window.
10. From the Database window, drag the Top_N_Sub report to the detail section of the Top_N_Per_Group report.
11. Click the Top_N_Sub report and open its property sheet.
12. Delete CategoryID from the LinkChild and LinkMaster properties.
13. Preview the report. Note that the Top_N_Sub subreport displays the top five selling products for each category.