ACC2000: How to Create a Top 10 Report

ID: Q231802

The information in this article applies to:

Microsoft Access 2000

This article applies to a Microsoft Access database (.mdb) and 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.

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: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

Make a copy of the "Sales by Category" query and name it "Top Ten Sales by Category."

Open "Top Ten Sales by Category" query in Design view.

On the View menu, click Properties.

Change the TopValue property to 10.

For the Product Name column, remove the value in the Sort row.

For the ProductSales: Extended Price column, set the Sort row to Descending.

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.

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.

Additional query words: ten

Keywords : kbdta RptOthr
Version : WINDOWS:2000
Platform : WINDOWS
Issue type :