ACC: How to Create a Top 10 Report

MORE INFORMATION

The following example demonstrates how to create a Top 10 report using a calculated control's RunningSum property to provide a dynamic counter for each record. A macro called from the report's OnFormat property then cancels the formatting of records whose counter falls outside the range you want to print:

1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).
2. Create the following new report, and then save it as Top 10 Report:
   

         Report: Top 10 Report
         -------------------------------
         RecordSource: Sales By Category
   
         Detail Section
         ----------------------
         OnFormat: Top 10 Macro
   
         Text Box
         ---------------------------
         Name: ProductSales
         ControlSource: ProductSales
   
         NOTE: In Microsoft Access 1.x and 2.0, there is a space in
         Product Sales.
   
         NOTE: In Microsoft Access 1.x, the Name property is called the
         ControlName property.
   
         Text Box
         ---------------------------
         Name: CategoryName
         ControlSource: CategoryName
   
         NOTE: In versions 1.x and 2.0, there is a space in Category Name.
   
         Text Box
         --------------------------
         Name: ProductName
         ControlSource: ProductName
   
         NOTE: In versions 1.x and 2.0, there is a space in Product Name.
   
         Text Box
         --------------------
         ControlName: Counter
         ControlSource: =1
         Visible: No
         RunningSum: Over All
   						

3. On the View menu, click Sorting And Grouping. Enter the following in the Sorting And Grouping window and then close it:
   

         Field/Expression: ProductSales
         Sort Order: Descending
   						

4. Create the following new macro, and then save it as Top 10 Macro:
   

         Macro: Top 10 Macro
         -----------------------
         Condition: [Counter]>10
         Action: CancelEvent
   						

5. Preview the report. The counter control will number each record, and the macro will prevent any record whose counter is greater than 10 from being printed. Because the report is sorted on ProductSales, the 10 largest sales will be printed.

Notes

• If you use this method with a Grouping/Totals report, the totals will include the records that are not printed, so the totals may not appear to be correct. To avoid this discrepancy, you can create a dynamic counter in the query. For additional information about this method, please see the following article in the Microsoft Knowledge Base:
  
  94397 ACC: Adding a Dynamic Counter to a Query to Count Records (2.0)
• You can use this method with a Grouping/Totals report to show the top 10 records per group. To do this, change the counter control's RunningSum property from Over All to Over Group.
• You can use this method with a Grouping/Totals report to show the top 10 groups. To do this, add the counter control to the report's group header instead of to the detail section, and add the macro to the OnFormat property of the group header, the detail section, and the group footer.