Microsoft KB Archive/231802

= ACC2000: How to Create a Top 10 Report =

Article ID: 231802

Article Last Modified on 12/12/2002

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q231802



This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

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



SUMMARY
This article shows you how to create a Top 10 report, which is a report that lists the first 10 records, based on the sort order of a report.

NOTE: This article explains a technique demonstrated in the sample file RptSmp00.exe. For information about how to obtain the sample file, please see the following article in the Microsoft Knowledge Base:

231851 ACC2000: Microsoft Access Sample Reports Available in Download Center



MORE INFORMATION
You can create a Top 10 report by using a top values query as the record source of the report. A top values query enables you to specify a specific number or percentage of records to return.

Creating the Top Values Query
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.


 * 1) Make a copy of the "Sales by Category" query and name it "Top Ten Sales by Category."
 * 2) Open "Top Ten Sales by Category" query in Design view.
 * 3) On the View menu, click Properties.
 * 4) Change the TopValue property to 10.
 * 5) For the Product Name column, remove the value in the Sort row.
 * 6) For the ProductSales: Extended Price column, set the Sort row to Descending.
 * 7) Save and close the query.

Creating the Top 10 Report
 Open the sample database Northwind.mdb.  Create the following new report, and then save it as "Top 10 Report":   Report: Top 10 Report ---  RecordSource: Top Ten Sales By Category

Text Box ControlName: Counter ControlSource: =1 RunningSum: Over All Label: Rank

Text Box ---  Name: ProductSales ControlSource: ProductSales

Text Box ---  Name: CategoryName ControlSource: CategoryName

Text Box --  Name: ProductName ControlSource: ProductName   On the View menu, click Sorting and Grouping. Enter the following in the Sorting and Grouping dialog box, and then close it:   Field/Expression: ProductSales Sort Order: Descending  Preview the report. Note that the counter control numbers each record and that only the top 10 records by ProductSales are displayed.

<div class="references_section">