Show All

Show only the high or low values in a query (MDB)

Note  The information in this topic applies only to a Microsoft Access database (.mdb).

You can show in a query datasheet those records with the highest or lowest values in a field or the highest or lowest percentage of values in the field. For example, you may want to show the top 10 salespeople with the highest total sales for a month, or the bottom 20 percent of students in a class by grade average.

1. Create a query in Design view.

   How?

   1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
   2. In the New Query dialog box, click Design View, and then click OK.
   3. In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
   4. Double-click the name of each object you want to add to the query, and then click Close.
   5. Add fields to the Field row in the design grid, and if you want, specify criteria and a sort order.
   6. To view the query's results, click View on the toolbar.
2. Add to the design grid the fields you want to display in the query's results, including the field you want to display top values for.
3. In the Sort cell of the field you want to display top values for, click Descending to display the highest values or Ascending to display the lowest values. If you're sorting on other fields in the query's design grid as well, those fields must be to the right of the top values field.
4. Click in the Top Values box on the toolbar.
5. Enter the percentage or the number of highest or lowest values you want the query results to display.

   Note  To display a percentage, enter a number followed by a percent sign (%).

   

6. To view the query's results, click View on the toolbar.

Note  Setting the TopValues property in the query's property sheet works the same as using the Top Values button on the toolbar.