You are currently offline, waiting for your internet to reconnect

How to create a top values per group report in Access 2002

This article was previously published under Q296449
This article has been archived. It is offered "as is" and will no longer be updated.
This article applies only to a Microsoft Access database (.mdb).

Novice: Requires knowledge of the user interface on single-user computers.

For a Microsoft Access 97 version of this article, see 132056.
For a Microsoft Access 2000 version of this article, see 208822.
SUMMARY
This article demonstrates how you can create a report that ranks a specificnumber of records for each group in order according to a top value, forexample, the top five selling products per category.
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. By 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.

For an example of how to create a top values per group report, follow these steps:
  1. Open the sample database Northwind.mdb.
  2. Create a new, blank report based on the Categories table, and then open it in Design view.
  3. If the field list is not displayed, on the View menu, click Field List, and then drag the CategoryID and CategoryName fields from the field list to the detail section of the report. Save the report as Top_N_Per_Group, and then close it.
  4. Create a new query that is 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      Field: CategoryID   Table: Products   Total: Group By   Criteria: [Reports]![Top_N_Per_Group]![CategoryID]   Field: ProductName   Table: Products   Total: Group By   Field: SaleAmount: Sum([Order Details].[UnitPrice]*[Quantity])   Total: Expression   Sort: Descending						
    NOTE: Referencing the CategoryID from the Top_N_Per_Group report ascriteria ensures that this query filters records by CategoryID before it applies the TopValues property to the recordset.
  5. Save the query as Top_N_Products, and then close it.
  6. Create a new, blank report that is based on the Top_N_Products query.
  7. If the field list is not displayed, on the View menu, click Field List, and then drag the ProductName and SaleAmount fields from the field list to the detail section of the report. Save the report as Top_N_Sub, and then close it.
  8. Open the Top_N_Per_Group report in Design view.
  9. On the Window menu, click Tile Horizontally.
  10. Drag the Top_N_Sub report from the Database window to the detail section of the Top_N_Per_Group report.
  11. On the Window menu, click Cascade.
  12. Click the Top_N_Sub report, and then open its property sheet.
  13. Delete CategoryID from the LinkChild and LinkMaster properties.
  14. Preview the report. Note that the Top_N_Sub subreport displays the top five selling products for each category.
REFERENCES
For more information about the TopValues property, click Microsoft Access Help on the Help menu, type topvalues property in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
OfficeKBHowTo inf
Properties

Article ID: 296449 - Last Review: 12/06/2015 02:12:46 - Revision: 4.0

  • Microsoft Access 2002 Standard Edition
  • kbnosurvey kbarchive kbhowto kbdta kbreport KB296449
Feedback