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 (http://support.microsoft.com/kb/145777/EN-US/) ACC95: Microsoft Access Sample Reports Available in Download Center
175072 (http://support.microsoft.com/kb/175072/EN-US/) ACC97: Microsoft Access 97 Sample Reports Available in Download Center
Back to the top
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.
|
Back to the top
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 (http://support.microsoft.com/kb/94397/EN-US/) 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.
|
Back to the top
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.
Back to the top
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Help and Support
