Display Top or Bottom Values

Telling Access to display only the top five values in a query.

Access displays the top five values it finds in the query.

Access displays the five tours with the lowest total sales.

This lesson explains how you can use the Top Values list to display the top or in a query.

1. Click the qryTourSales query and then click the Design button.

   Move to the next step and add the fields you want to see in your query.

2. Double-click the TourName field in the tblTours field list, and the Cost and Smoker fields in the tblCustomerTours field list.

   You want to create a query that retrieves the five most expensive nonsmoking tours. First you need to add the nonsmoking criteria to the query.

3. Click the Criteria row in the Smoker column and type False.

   Now you need to sort the field that you want to display the top or bottom values for. The Sort row works a little differently when you're using top or bottom values:

   • Ascending: Displays bottom values.

   • Descending: Displays .

   You want the query to display the top values in the Cost field, so...

4. In the Cost column, click the Sort box list arrow and select Descending.

   Next you have to use the Top Values list to specify the number of top values you want to be displayed in your query results.

5. Click the Top Values list arrow on the toolbar and select 5, as shown in figure.

   This will display the five most expensive tickets. You're ready to run the query.

6. Click the Run button on the toolbar.

   Access displays the results of the query, similar to figure.

7. Click the View button on the toolbar to display the query in Design view.

   You can also use the Top Values feature to display the top or bottom values from a calculation. Let's modify the query so that it calculates the total sales of nonsmoking tour sales.

8. If the Total row is not already displayed, click the Totals button on the toolbar.

   The Total row appears. You need to specify which field you want to use to group the query, which field(s) you want to be calculated, and which fields are used to limit the number of records displayed in the query.

   You want to group records using the TourName field, and since its Total row already displays Group By, you can leave it as it is. You will need to tweak the Total row for both the Cost and Smoker fields, however.

9. Click the Total row in the Cost column, click the list arrow, and select Sum from the list.

   This will total the Cost field. The Smoker field is used as criteria to limit the records displayed to only nonsmokersit shouldn't be included in the query results. Move on to the next step to tell Access this.

10. Click the Total row in the Smokers column, click the list arrow, and select Where from the list.

    One more change before we run the querythis time instead of displaying the top values for the Cost field we want to display its bottom values.

11. In the Cost column, click the Sort box list arrow and select Ascending.

    This will summarize the five tours with the lowest sales (for nonsmokers). Let's see the results...

12. Click the Run button on the toolbar.

    Access displays the results of the query, as shown in figure.

13. Save the query as qryBottomTours and then close the query.

Following table explains what each of the options in the Top Values list does.

The Top Values List

Do This...	...to Display This
Click 5, 25, or 100 from the Top Values List	The top 5, 25, or 100 records
Type a number, such as 15 in the Top Values box	The top 15 (or specified number of) records
Click 5% or 25% from the Top Values List	The top 5 or 25 percent of records
Type a percentage, such as 20%, in the Top Values box	The top 20 percent (or specified percent) of records
Click All from the Top Values list	All of the records

To view top or bottom values:

1. display the query in design view.

2. click the appropriate sort field and select either:

   • ascending: displays bottom values.

   • descending: displays top values.

3. select an option from the top values list on the toolbar.