ACC2000: How to Create a Top 10 Report (231802)


The information in this article applies to:

  • Microsoft Access 2000
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

  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.

REFERENCES

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.
Modification Type:MajorLast Reviewed:12/12/2002
Keywords:kbdta KB231802
©2002 Microsoft Corporation. All rights reserved.