Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Create a Top Values Per Group Report

ACC2000: How to Create a Top Values Per Group Report

View products that this article applies to.

This article was previously published under Q208822
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 2002 version of this article, see 296449� (http://support.microsoft.com/kb/296449/EN-US/ ) .

↑ Back to the top

Summary

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 file, RptSmp00.mdb. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:
231851� Microsoft Access 2000 Sample Reports Available in Download Center

↑ Back to the top

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.

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 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 detail section of the report. Close and save report as Top_N_Per_Group.
  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
       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 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 detail section of the report. Close and save the report as Top_N_Sub.
  8. Open the Top_N_Per_Group report in Design view.
  9. With the report open in Design view, press F11 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.

↑ Back to the top

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.

↑ Back to the top

Keywords: KB208822, kbusage, kbdta, kbhowto

↑ Back to the top

Article Info
Article ID : 208822
Revision : 1
Created on : 12/12/2002
Published on : 12/12/2002
Exists online : False
Views : 299