Show All

Hide All

You find records containing the top or bottom values in a field by creating a special type of query, called the top values query.

Some common scenarios where you will create a top values query are when you want to:

Identify the most expensive product in the list of products.   

Find the names of the three employees whose birthdays are coming up next.   

Identify the five cities that generated the greatest amount of sales during the current year.   

See the names of the bottom five percent of the products that sold the least during the current year.   

In each of the preceding scenarios, you are looking for records that contain the top or bottom value in a specific field, without actually specifying the top or bottom values. This is what distinguishes a top values query from a filter.

How does a top values query differ from a filter?

How does a top values query work in the absence of specific field values to compare against? First, the query sorts the records based on the field that contains the top or bottom values. Let's take the first scenario, where you want to identify the most expensive product. Here, the top values query will first sort the records by the Price field to list the products from most expensive to least expensive. Then it will return the number of records you want. In this case, because you want to see only the most expensive product, it will return the topmost record in the sorted list. If you want the names of the two most expensive products, the query will return the first two records.

Before you move to creating a top values query, take another look at the scenarios listed earlier to understand how flexible top values queries are:

• Some scenarios look for a single record, while others look for a specified number or percentage of records.
• Some scenarios look for records containing the highest values, while others look for records with the lowest values.
• Some scenarios look for records based on numeric values, while others look for records based on date values.

Based on the preceding information, a top values query can be summarized as a query that sorts and then filters records to return the required amount of records that contain the top or bottom values in a field in a given list of records.

What if you are not interested in just the most expensive product, but in the most expensive product in each category? Or, the name of one employee from each city whose birthday is coming up next? In other words, what if you want the records to be grouped based on a field, then sorted within each group, and then filtered to show the top or bottom record for each group?

In the preceding table, the CategoryName field is the group field, the ProductName field is the data field that identifies the product, and the Price field is the field that shows the top values.

If you want to return one record per group, you must create a totals query. A totals query groups, sorts, and filters records for you. To continue the scenario where you want to identify the most expensive product in each category, the totals query will first group records by category, then sort records within each category by the Price field, and then return the record with the maximum or minimum value in the Price field within each group.

And there are scenarios where you might be interested in records with both top and bottom values. For example, you might want to see the most expensive and least expensive products in one view. If you want to see the top and bottom values in a single query, you must create two different queries where one query retrieves the records with the top values and the other retrieves the records with the bottom values. Then you must create a table that stores the records from both queries and displays both query results in Datasheet view, as shown here.

What do you want to do?

Find the records with top or bottom values

Find the record with the top or bottom value within each group

Find the records with top and bottom values

Find the records with the top or bottom values

Let's take the scenario of finding the three employees whose birthdays are coming up next to step through this procedure. We will use the data from the sample Northwind Traders database.

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. Double-click the name of each object you want to add to the query, and then click Close. For our scenario, on the Tables tab, double-click the Employees table, and then close the dialog box.
4. Add the fields you want in your query to the design grid. To add the fields, double-click the field entries in the table and query windows displayed in the top half of the design window. You can add as many fields as you want, but the following fields must be included in the query:
   • Data fields   These are fields that contain the information you are looking for. For example, to find the employees whose birthdays are coming up next, include the fields that will identify the employee, such as FirstName and LastName.
   • Field that contains the top or bottom values   This is the field that contains the values that will be used for sorting the records. To continue with the example of identifying the birthdays, you must include the BirthDate field in the query. However, if you sort on the entire field values and then pick the top or bottom three values, you will get the three oldest or youngest employees, not the employees with the most recent birthdays. To sort on birthdays irrespective of the age of the employee, you need to ignore the year portion of the field values and sort only on the month and day portion of the values. To do this, do these additional steps:
     1. Add the month portion of the BirthDate field as a column so that you can sort on it. In the grid, in the column to the right of the BirthDate column, type the expression Expr1: DatePart("m",[BirthDate]). The DatePart function function extracts the month portion of the BirthDate field. Clear the check box in the Show row because this column is required only for sorting.
     2. Add the date portion of the Birthdate field as a column so that you can sort on it. In the grid, in the rightmost column, type the expression Expr2: DatePart("d",[BirthDate]). The DatePart function extracts the day portion of the BirthDate field. Clear the check box in the Show row because this column is required only for sorting.

        

5. Optionally, you can specify a criteria 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. Because we are interested only in the birthdays that are coming up next, we can ignore the birthdays that fall before today's date. If you run the query on June 25th, you will want to ignore all birthdays that occur between January 1 and June 25.

   Identify the fields that will be used for limiting the number of rows in the query. In the design grid, in the Criteria row of each such field, type an expression. For our scenario, in the Criteria row of the BirthDate field, type the following expression.

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

   

   The Month function extracts the month portion from a given date value. The Day function extracts the day portion from a given date value. The Date Function returns the date of the day on which you run the query.

   The Month([BirthDate]) > Month(Date()) portion of the expression 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 date of those employees whose birthday falls during the current month to see if the day of the birth date falls after the current day and, if true, includes those records in the query. To summarize, this expression will ignore the records where the birthday falls between January 1 and the date on which you run this query.

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

6. Specify whether you want records with top or bottom values. In the query design grid, set the Sort row of the field that has the top or bottom values to either Ascending or Descending. If the field is a numeric field, such as Price or Sales, specify Descending order to see the records with the highest values, and Ascending order to see the records with the smallest values. If the field is a Date/Time field, such as BirthDate, specify Descending order to see the records with the most recent dates, and Ascending order to see the records with the oldest dates.

   To continue our scenario, because we are sorting on partial date values, you should set the sort order for the columns that contain the partial values. Set the Sort rows of the Expr1 and Expr2 columns to Ascending to see the birthdays that fall after today. This will make the query sort the birthdays by month, and within each month, by day. The year will be ignored. The ascending sort order will place the smaller (or most recent) dates at the top.

   

   Important   The Sort row must be set only for the fields that contain the top or bottom values. If you specify a sort order for the data fields or the BirthDate field, the query will not return the desired results.

7. In the Top Values box on the Query Design toolbar, specify the number of records you want to see. To see a fixed number of records, type a number, such as 3 or 5. To see a percentage of records, enter the number followed by a percent sign (%), such as 5% or 10%. For our example, type 3 to see three employee names.
8. Run the query by clicking View on the toolbar. If the date on which you run the query is 6/25/2004, you will see the following records:

   FirstName	LastName	BirthDate
   Anne	Dodsworth	02-Jul-1969
   Michael	Suyama	02-Jul-1963
   Janet	Leverling	30-Aug-1963

   Why you might see more records than you expect

   This happens in the following situations:

   • The smallest value that qualifies for inclusion in a query that returns the largest n values appears in multiple records.
   • The largest value that qualifies for inclusion in a query that returns the smallest n values appears in multiple records.

   In both these situations, the query includes all such records, even though the total number of included records exceeds the number you requested.

   Here's an example of a top values query that is defined to retrieve the names of three employee records, but actually returns four. The query cannot ignore "Wilson" or "Edwards" because their birthdays fall on the same day.

   LastName	BirthDate
   Johnson	9/26/1968
   Jackson	10/2/1970
   Edwards	10/15/1965
   Wilson	10/15/1969

   Here's another example of a query that is defined to retrieve the names of four employees who celebrate their birthdays in the beginning of each year, but actually returns five names. This is because three employees share the same birthday.

   ProductName	Price
   Erikson	1/7/1978
   Alverson	1/31/1968
   Peterson	2/8/1975
   Murchison	2/8/1969
   Winston	2/8/1971

   ---

   Why you might see fewer records than you expect

   Either the total number of records in the query is less than what you requested, or the query includes criteria that excludes certain records. Open the query in Design view and review the Criteria row of the fields in the design grid.

   ---

   Why you might see duplicate records in the view

   Either the underlying tables contain duplicate records, or records appear to be duplicates because the query does not include the fields that can distinguish between the records. For example, here's a query that shows the five orders that were shipped most recently, along with the name of the salesperson who handled the transaction.

   ShippedDate	Salesperson
   11/12/2004	Fuller
   11/12/2004	Callahan
   10/12/2004	Davolio
   10/12/2004	Davolio
   10/12/2004	Fuller

   The third and fourth records appear to be duplicates, but it is possible the salesperson Davolio handled two different orders that shipped on the same day.

   Depending on your requirements, you can do one of two things to avoid returning duplicate records. You can change the design of the query to add fields that will help distinguish the records in Datasheet view, such as the OrderID and CustomerID fields. Or, if it is sufficient to see just one of the duplicate records, you can show only distinct records by setting the query's UniqueValues Property to Yes. To set this property, in query Design view, click anywhere in the gray area in the top half of the window, and then click Properties on the Query Design toolbar. Then set the UniqueValues property to Yes.

   ---

Find the record with the top or bottom value within each group

You do this by creating a totals query. The important thing to note here is that a totals query can only include group fields and the field that has the top or bottom value. It cannot include other fields that describe the top or bottom value.

For example, if you create a totals query to identify the most expensive product in each category, the query can only include the CategoryName field (group field) and the UnitPrice field (the field whose top or bottom values you are interested in), as shown in the following table.

You cannot include the ProductName field, or fields such as the CompanyName field, to get more information about the product whose price is displayed in the query. If you need individual data fields that describe the top or bottom value, you need to create a second select query that is based on the totals query. The second query will bring together the fields in the totals query and the data fields from the source tables and queries.

To make it easier to understand, let's create the queries necessary to identify the most expensive products in each product category by using the sample database, Northwind.mdb.

Create the Top Price per Category totals query

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. Then, double-click the name of each object you want to add to the query, and then click Close. For our example, add the Products and Categories tables, and then close the Show Table window.
4. Add the fields to the design grid. To add fields, double-click the field entries in the table and query windows displayed in the top half of the design window. Make sure you add only the following fields to the query:
   • Group fields   These are the fields by which you want to group records. To find the most expensive product in each category, add the CategoryName field to the grid. If, in addition, you want to see the most expensive product supplied by each supplier in each category, you will add the CategoryName field from the Categories table and the SupplierName field from the Suppliers table to the grid.
   • Field that contains the top or bottom value   This is the field that contains the values that will be used for sorting the records. Using our example, add the Price field to the grid.

   

5. Optionally, you can specify a criteria 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, if you want to exclude seafood products when identifying the most expensive products, you might specify a criteria to exclude all products where the category field is set to Seafood.

   Learn how to specify a criteria for one or more fields

   Identify the fields that will be used for limiting the number of rows in the query. For example, if you want to exclude the seafood category, the Category field becomes the criteria field.

   In the design grid, in the Criteria row of each such field, type an expression. For example, to exclude the Seafood category, in the Category column, type <> "Seafood". To exclude products that cost less than $100, type >=100 in the Price column.

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

   ---

6. Next, you need to specify how the records must be grouped and whether you want the record with the top or bottom value. Click Totals on the Query Design toolbar to add the Totals row to the design grid.
7. Set the Totals row of each group field column to Group By.
8. Set the Totals row of the field that has the top or bottom value to either Max or Min. Max returns the largest value in a numeric field and the most recent date or time value in a Date/Time field. Min returns the smallest value in a numeric field and the earliest date or time value in a Date/Time field.

   In our example, set the Totals row of the CategoryName field to Group By and the Totals row of the field to Max.

   

9. Make sure that the Top Values box on the Query Design toolbar is set to All.
10. Run the query by clicking View on the toolbar.

    You will see the following records and fields returned in the datasheet.

    CategoryName	MaxOfPrice
    Beverages	$263.50
    Condiments	$43.90
    Confections	$81.00
    Dairy Products	$55.00
    Grains/Cereals	$38.00
    Meat/Poultry	$123.79
    Produce	$53.00
    Seafood	$62.50

The query does not show the name of the product or any other information. If you are interested in additional details, such as the name of the product and the name of the supplier, you need to create a second query that incorporates the query you just created.

Create a second query to bring together fields in the totals query and other data fields

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 Queries tab, and then double-click the totals query that you last created. Next, add the tables and queries on which the totals query is based. In this case, the totals query is based on the Categories and Products tables, so add those two tables to the query window.
4. Next, add the tables that contain the product name and supplier name information that you want to include. You'll need to add the ProductName field from the Products table and the CompanyName field from the Suppliers table to the query. Because the Products table has already been added, you only need to add the Suppliers table. On the Query menu, click Show Table and add the Suppliers table.
5. Join the totals query to the other tables. Joining helps the new query bring the data in the totals query and the other tables together. Create a join based on each field in the totals query. For our example, drag the CategoryName field in the totals query to the CategoryName field in the Categories table. Next, drag the MaxOfPrice field in the totals query to the Price field in the Products table. This will make it possible for the second query to get to the matching category and product records for each record in the totals query.

   

6. Add the fields to the design grid. To add fields, double-click the field entries in the table and query windows displayed in the top half of the design window. Make sure you add the following fields to the design grid:
   • The fields in the totals query   Add all the fields that are part of the totals query. Because we want the CategoryName and MaxOfPrice fields from the totals query in the final view, add these fields to the grid.
   • The data fields   These are the additional fields you want in the query. For our example, add the ProductName field from the Products table and the CompanyName field from the Suppliers table to the grid.

   

7. Optionally, you can specify a sort order for one or more of the columns. Because this query is a typical select query, the sort order will not affect the selection of records, just the order of records. If you want to see the categories listed in alphabetical order, set the Sort row of the CategoryName column to Ascending.
8. Run the query by clicking View on the toolbar.

You should see the following records and fields in the datasheet view.

Tip   If you don't want the heading of the Price column to appear as MaxOfPrice, open the query in Design view, and in the MaxOfPrice column in the grid, type Price: MaxOfPrice. Price will now appear as the heading of the second column in Datasheet view.

Find the records with the top and bottom values

The top values query and the totals query can select only one type of value — either top or bottom values. If you want to see both sets of values in a single view, you need to create two queries — one that retrieves the top values and another that retrieves the bottom values — and then merge and store the results in a single table. You can then open this new table in Datasheet view to see both top and bottom values in one place. A table that brings together the most expensive and least expensive products will look like the following.

Here are the steps for creating the necessary queries and the final table.

1. Create the top and bottom queries. If you want to see the records with the top and bottom values for each group, create two totals queries and two select queries. For step-by-step information about how to create these queries, see the section Find the record with the top or bottom value within each group. If grouping is not necessary, create two top values queries. For step-by-step information about how to create a top values query, see the section Find the records with top or bottom values.

   Let's assume that the two select queries or the top values queries are named Top Value and Bottom Value.

   The Top Value query returns one row with two fields.

   ProductName	Price
   Côte de Blaye	$263.50

   The Bottom Value query returns one row with two fields.

   ProductName	Price
   Geitost	$2.50

2. Open the Top Value query in Design view. In the rightmost column in the grid, type the string TypeofPrice: "Most Expensive" in the Field row. This column helps identify each field value as the top or bottom value in the combined view. This might not be necessary if you are selecting just the top and bottom values, but it is useful if you are selecting several top records and several bottom records.

   ProductName	Price	TypeOfPrice
   Côte de Blaye	$263.50	Most Expensive

3. Click the arrow next to the Query Type button on the Query Design toolbar, and then click Make-Table Query.
4. In the Make Table dialog 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 will replace the contents of the Top and Bottom Records table with the records that contain the top values.
5. Save and close the Top Value query.
6. Open the Bottom Value query in Design view. In the rightmost column in the grid, type the string TypeOfPrice: "Least Expensive" in the Field row.
7. Click the arrow next to the Query Type button on the Query Design toolbar, and then click Append Query.
8. 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 will append the records to the Top and Bottom Records table.
9. Save and close the Bottom Value query.
10. You are now ready to run the two queries. In the Database window, double-click the Top Value query and click Yes in the messages. Then double-click the Bottom Value query and click Yes in the messages.
11. Now open the Top and Bottom Records table in Datasheet view. You should see the top value record followed by the bottom value record. The last column in the datasheet will display "Most Expensive" or "Least Expensive," depending on the type of value.

    ProductName	Price	TypeOfPrice
    Côte de Blaye	$263.50	Most Expensive
    Geitost	$2.50	Least Expensive

Additional resources

Creating a top values or totals query programmatically   If you are interested in creating a top values query or totals query programmatically, see the following topics:

• TopValues Property
• UniqueValues Property
• GROUP BY Clause
• Min function
• Max function

To get help about more functions and properties, see the topics Functions (alphabetical) and Properties. For a list of events, see the topic Events.

Ranking the records in a query   If you are interested in ranking the records in a query based on the values in a field and then displaying the ranks in Datasheet view, follow the link to the Microsoft Knowledge Base article How to rank records within a query in the See Also box. This article explains how to add and populate a numeric rank field for each query record. Knowledge of writing code by using Microsoft Visual Basic for Applications (VBA) is not required to complete the procedures outlined in this article.