ACC2000: How to Create a Top 10 Report

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:

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

1. Open the sample database Northwind.mdb.
2. 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
   					

3. 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
   					

4. Preview the report. Note that the counter control numbers each record and that only the top 10 records by ProductSales are displayed.

For more information about the TopValues property, click Microsoft Access Help on the Help menu, type top values in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about sorting data in reports, click Microsoft Access Help on the Help menu, type sort records in a report in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.