ACC: How to Create a Top 10 Report

Last reviewed: October 24, 1997
Article ID: Q109319
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

This article describes a method for creating a Top 10 report, which is a report that lists the first 10 records, based on the report's sort order.

NOTE: In Microsoft Access 2.0, 7.0 and 97, you can base a report on a top values query.

For more information about TopValues, search for "TopValues Property" using the Microsoft Access Help Index.

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:

   ARTICLE-ID: Q145777
   TITLE     : ACC95: Microsoft Access Sample Reports Available on MSL

   ARTICLE-ID: Q175072
   TITLE     : ACC97: Microsoft Access 97 Sample Reports Available on MSL

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:

          ARTICLE-ID: Q94397
    
          TITLE     : 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.

REFERENCES

For more information about sorting data in reports, search for "sorting data," and then "Sort records in a report" using the Microsoft Access 97 Help Index.


Additional query words: ten
Keywords : RptOthr kbusage
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: October 24, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.