Microsoft KB Archive/109319

= ACC: How to Create a Top 10 Report =

Article ID: 109319

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q109319



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



SUMMARY
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:

145777 ACC95: Microsoft Access Sample Reports Available in Download Center

175072 ACC97: Microsoft Access 97 Sample Reports Available in Download Center



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:

 Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).  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   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   Create the following new macro, and then save it as Top 10 Macro:

     Macro: Top 10 Macro ---     Condition: [Counter]>10 Action: CancelEvent  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.</li></ol>