Find the records with the top or bottom values in a group or field

Choosing between a top values query and a filter

To determine whether you should create a top values query or apply a filter, consider the following:

• If you want to return the records that contain the highest or lowest values in a field, and you do not know the exact top or bottom field values (or they don't matter), you create a top values query.

• If you want to return all the records where the value in a field matches, or is either less than or greater than a value in which you are interested, you apply a filter. For example, to see the products with prices between $50 and $100, you apply a filter. This figure illustrates a sample filter in query Design view and the results that such a filter can produce:

  

  For more information about creating and using filters, see the article Apply a filter to view select records in an Access database.

Rules for creating and using top values queries

As you proceed, remember that, regardless of query type (a select query or a totals query), you must use fields that contain descriptive data, such as employee or product names, and a field that contains the top or bottom values that you want to find, such as a price or a date field. You can use more than one data field and, if you use a totals query, your data fields usually should include category information, such as a city or country/region field. You can omit category data only when you create a totals query that runs against all records, such as "show me the top five percent of the most expensive orders." Also, you can use either a select or a totals query to find the top and bottom values in a group of records or records that fall into categories. For more information about doing so, see the section Find the top or bottom values for records in categories or groups, later in this article.

Create a basic top values query

1. On the Create tab, in the Queries group, click Query Design.

2. Click the table that you want to use in the query, click Add to place the table in the design grid, and then click Close.
   -or-
   Double-click the table.
   If you use the sample data, add the Employees table to the query.

3. Add the fields that you want to use in your query to the design grid. You can double-click each field, or drag and drop each field on a blank cell in the Field row.
   If you use the sample table, add the First Name, Last Name, and Birth Date fields.

4. In the field that contains your top or bottom values (the Birth Date field, if you use the sample table), click the Sort row and select either Ascending or Descending.
   If your values field contains numbers, such as prices or sales figures, the Ascending sort order returns the lowest values, and the Descending sort order returns the highest values. If your values field contains dates, the Descending sort order returns the most recent date, while Ascending returns the earliest date.
   

   Important: You must set the Sort row to Ascending or Descending only for the fields that contain your top or bottom values. If you specify a sort order for a field other than the field containing your top or bottom values — for example, ProductName instead of Price — the query will not return the results you want.

5. On the Design tab, in the Query Setup group, click the down arrow next to Return (the Top Values list), and either enter the number or percentage of records that you want to see, or select an option from the list.

6. Click Run to run the query and display the results in Datasheet view.

7. Save the query and keep it open for use in the next steps.

You can see that this type of top values query can answer basic questions, such as who is the oldest or youngest person in the company. The next steps explain how to use expressions and other criteria to add power and flexibility to the query. The criteria shown in the next step return the next three employee birthdays.

Add criteria to the query

1. Open, in Design view, the query that you created in the previous steps.

2. In the query design grid, in the column to the right of the Birth Date column, copy and paste or type the expression Expr1: DatePart("m",[Birth Date]). The DatePart function extracts the month portion of the value in the Birth Date field.

3. To the right of the column that contains your expression, copy and paste or type the expression Expr2: DatePart("d",[Birth Date]). In this case, the DatePart function extracts the day portion of the value in the Birth Date field.

4. Clear the check boxes in the Show row for each expression, and then click the Sort cell for each expression and select Ascending.

5. Optionally, you can specify a criteria to limit the scope of the query. When you specify criteria, the query sorts only the records that meet the criteria, and it identifies the top or bottom field values from within the sorted list.
   To continue with the sample data, in the Criteria row of the Birth Date field, type or copy and paste this expression:

   Month([Birth Date]) > Month(Date()) Or Month([Birth Date])= Month(Date()) And Day([Birth Date])>Day(Date())

   This expression does the following: The Month([BirthDate]) > Month(Date()) portion checks the birth date of each employee to see if it falls in a future month and, if true, includes those records in the query. The Month([BirthDate])= Month(Date()) And Day([BirthDate])>Day(Date()) portion of the expression checks the birth dates that occur in the current month to see if the birth date falls on or after the current date. If that condition is true, the function includes those records in the query results. To summarize, this expression ignores any records where the birth date falls between January 1 and the date on which you run your query.

   To see more examples of query criteria expressions, see the article Examples of query criteria.

6. On the Design tab, in the Query Setup group, click the down arrow next to Return (the Top Values list), and either enter the number or percentage of records that you want to see, or select an option from the list.

7. Click Run to run the query and display the results in Datasheet view.

Create the totals query

1. On the Create tab, in the Queries group, click Query Design.

2. Select the tables that you want to work with, click Add, and then click Close after you have finished adding the tables.
   -or-
   Double-click the tables that you want to use, and then click Close. Each table appears in the design grid. If you use the sample tables listed above, you add the Categories and Products tables.

3. Double-click the table fields that you want to use in your query.
   At this point, ensure that you add only the group fields and the value field to the query. If you use the sample data listed in the preceding table, you add the Category Name field from the Categories table, and the Price field from the Products table.

4. Optionally, you can specify a criterion to limit the scope of the query.
   Only records that meet the criteria will be sorted, and top and bottom field values will be identified within this sorted list. For example, to exclude products in the Sports category, you add this expression to the Criteria row of the Category Name field: <>"Sports".
   To see more examples of query criteria expressions, see the article Examples of query criteria.

5. Convert the query to a totals query.

   1. On the Design tab, in the Show/Hide group, click Totals.

      The Total row appears in the design grid.

6. Ensure that the Total cell of each group field is set to Group By, and then set the Total cell of the value field (the field containing the top or bottom values) to either Max or Min.
   Selecting Max returns the largest value in a numeric field and the most recent date or time value in a Date/Time field. Selecting Min returns the smallest value in a numeric field and the earliest date or time value in a Date/Time field. If you use the sample data, set the Total cell of the Category Name field to Group By, and the Total cell of the Price field to Max or Min.

7. On the Design tab, in the Tools group, make sure that the Return list is set to All, and then click Run to run the query and display the results in Datasheet view.
   

   Note: Depending on the function that you chose in step 7, Access changes the name of the value field in the query to MaxOfFieldName or MinOfFieldName. If you use the sample tables, Access renames the field MaxOfPrice or MinOfPrice.

8. Save the query and go to the next steps.

The query does not return product names or any other information about the products. To see that additional data, you need to create a second query that incorporates the query you just created. The next steps explain how to create this second query.

Create a second query to view more data

1. On the Create tab, in the Queries group, click Query Design.

2. Click the Queries tab, and then double-click the totals query that you created in the previous section.

3. Click the Tables tab and add the tables that you used in your totals query plus any tables that contain the other related data that you want to see.
   If you use the sample tables listed earlier, you add the Categories, Products, and Suppliers tables to your new query.

4. Join the fields in the totals query to their corresponding fields in the parent tables. To do so, drag each field in the totals query to its corresponding field in the table.

   If you use the sample data, drag the Category Name field in the totals query to the Category Name field in the Categories table. You then drag the MaxOfPrice field in the totals query to the Price field in the Products table. The joins enable your new select query to bring together the data in the totals query and the data in the other tables.

5. In the window for the totals query, double-click the asterisk to add the entire query to the design grid, and then drag the additional fields from the other tables in the query.

   If you use the sample tables, you double-click the totals query that you created in the previous section and then add the Product Name and Supplier fields from the Products and Suppliers table, respectively.

6. Optionally, specify a sort order for one or more of the columns.
   For example, to list the categories in alphabetical order, set the Sort cell of the Category Name column to Ascending.

7. On the Design tab, in the Results group, click Run.

   This runs the query and displays the results in Datasheet view.

Top of Page

Create the make table query

1. With your top values query open in Design view:

   On the Design tab, in the Query Type group, click Make Table.
   The Make Table dialog box appears.

2. In the Table Name box, type a name for the table that will store the top and bottom records. For example, type Top and Bottom Records, and then click OK.
   Each time you run the query, instead of showing the results in Datasheet view, the query creates a table and replaces the top value with the current data.

3. Save and close the query.

Create an append query

1. Open your bottom value query in Design view.

2. On the Design tab, in the Query Type group, click Append.

3. In the Append dialog box, type the same name that you typed in the Make Table dialog box.
   For example, type Top and Bottom Records and then click OK. Each time you run the query, instead of showing the results in Datasheet view, the query appends the records to the Top and Bottom Records table.

4. Save and close the query.

Run the queries

You are now ready to run the two queries.

• In the Navigation Pane, double-click the top value query and click Yes in the messages.

• Double-click the Bottom Value query and click Yes in the messages.

• Open the top and bottom records table in Datasheet view.

Top of Page