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

This article explains how to use a top value query in Access to find the highest or lowest values in a set of records. You use top value queries to answer a variety of business questions, such as which products sell well and which do not.

What do you want to do?

• Understand top value queries

• Find the records that contain top or bottom values

• Find the top or bottom values for records in categories or groups

• Find the records that contain top and bottom values

Understand top value queries

You use a top value query when you need to find records that contain the top or bottom values in a table field. You can use a top value query to answer the following types of questions.

• Which is the most or least expensive product?

• Which three employees have the next birthdays?

• Which divisions generated the greatest or least sales during the current year?

• Which products occupy the top or bottom five percent of sales?

• Which products in each of the product categories occupy the top or bottom five percent of sales?

Put briefly, a top-values query sorts and then filters your data to return the top or bottom values in a field. You can use a top values queries to search for numeric (including currency) and date values.

The process of creating a top values query follows these broad steps:

• Create either a select query or a totals query.

  You use a select query to find top or bottom values in a table. You use a totals query to find the top or bottom values in in one or more categories. For example, if you want to answer a question such as "What is the most or least expensive product," you start by creating a select query. To answer a question such as "Which products in each of our product categories occupy the top or bottom five percent of sales," you use a totals query. Also, you can use either type of query to find the top and bottom values in either a group of records or records that fall into categories.

• Apply a sort order to the query.

• Apply other criteria to the query.

• Specify the number of records that you want the query to return. You can specify a percentage, such as the top 5 percent of values in a field, or you can specify a fixed number, such as the top 10 values in a 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.

Find the records that contain top or bottom values

The steps in this section explain how to create a basic top values query and a more advanced query. The first steps demonstrate the basic parts of a top values query, while the second section explains how to find the next few employee birthdays. The steps use the following sample table:

Last Name	First Name	Address	City	Country/region	Birth Date	Hire Date
Barnhill	Josh	1 Main St.	New York	USA	05-Feb-1968	10-Jun-1994
Heloo	Waleed	52 1st St.	Boston	USA	22-May-1957	22-Nov-1996
Guido	Pica	3122 75th Ave. S.W.	Seattle	USA	11-Nov-1960	11-Mar-2000
Bagel	Jean Philippe	1 Contoso Blvd.	London	UK	22-Mar-1964	22-Jun-1998
Price	Julian	Calle Smith 2	Mexico City	Mexico	05-Jun-1972	05-Jan-2002
Hughes	Christine	3122 75th St. S.	Seattle	USA	23-Jan-1970	23-Apr-1999
Riley	Steve	67 Big St.	Tampa	USA	14-Apr-1964	14-Oct-2004
Birkby	Dana	2 Nosey Pkwy	Portland	USA	29-Oct-1959	29-Mar-1997
Bagel	Jean Philippe	1 Contoso Blvd.	London	UK	22-Mar-1964	20-Jun-1998

If you want, you can enter the data in this sample table manually, or you can copy this table to a spreadsheet program, such as Microsoft Excel, and then import the resulting worksheet into a table in Access.

Note: If you don't have a spreadsheet program, you can copy the sample data to a text editor, such as Notepad. For more information about importing text data, see the article Import or link to data in a text file.

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

Note: These steps assume that you will use the query described in the previous section.

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.

If your query returns more records than you expect

If your data contains records that share a date value, your top values query might return more data than you expect. For example, you can design a top values query to retrieve three employee records, but you actually see four because "Wilson" and "Edwards" share a birthday. This type of query returns all top values, no matter how many records have those values. To exclude duplicate values, you can set a property called Unique Values to Yes. For information on using that property, see the section If you see duplicate records, later in this article.

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

If your query returns fewer records than you expect

Suppose that you design a query to return the top or bottom five records in a field, but you only see three of the records. As a rule, you solve that type of problem by opening the query in Design view and reviewing the Criteria row in the design grid for criteria that are more restrictive than you intended. For more information about criteria, see the article Examples of query criteria.

If you see duplicate records

If a top values query returns duplicates, 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	Fontana
11/12/2004	Moreno
10/12/2004	Osada
10/12/2004	Osada
10/12/2004	Fontana

The third and fourth records appear to be duplicates, but possibly because the salesperson Osada 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, 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 Unique Values Property to Yes. To set this property, in query Design view, press F4 to display the property sheet for the query, locate the Unique Values property and set it to Yes.

For more information about dealing with duplicate records, see the article Find duplicate records with a query.

Top of Page

Find the top or bottom values for records in categories or groups

You find the top or bottom values for records that fall into groups by creating a totals query. As you proceed, remember that, by default, a totals query can include only the field or fields that contain your group data, such as a "categories" field, and the field that has the top or bottom values, such as a "price" field. Totals queries cannot include other fields that describe the items in a category. However, you can create a second query that includes fields from your totals query, plus fields from other tables that contain descriptive data.

For example, suppose that you have a table of products, and you assign each product to a category, such as Board Games, Puzzles, and so on. If you create a totals query to identify the most expensive product in each category, the query can only include the field or fields that contain the category data and the field that contains the price data. For this example, assume a field called Category Name and a field called Price. When you run this type of query, Access appends "MaxOf" or "MinOf" (depending on the function that you choose) to the beginning of the Price field, like so:

Category Name	MaxOfPrice
Board Games	$55.00
Puzzles	$43.90
Computer Games	$263.50
Dolls	$81.00
...	...

Your totals query cannot include other fields that would help describe the product (and thus make the data easier to understand), such as product names or the names of suppliers, like so:

Category Name	MaxOfPrice	Product Name	Supplier Name
Board Games	$55.000	Fun with C++	Proseware, Inc.
Puzzles	$43.90	Relational Database Diagram	Lucerne Publishing
Computer Games	$263.50	Computer Geeks and Mythical Creatures	Litware, Inc.
Dolls	$81.00	Programmer Action Figure	Contoso, Ltd
...	...	...	...

If you want to see that descriptive data, you can create a second select query that combines the fields in your totals query with the additional data fields.

The steps in this section explain how to create the totals query and select query needed to identify the most expensive products in each a set of categories. The steps assume the use of the following sample tables:

The Categories table

CategoryID	Category Name	Description
1	Board Games	All ages and skill levels
2	Puzzles	Jigsaw, word puzzles, puzzle toys
3	Computer Games	All consoles and skill levels
4	Dolls	Action figures, fashion dolls
5	Sports Equipment	Balls, clothes, nets
6	Models/Hobby	Planes, cars, trains

The Suppliers table

SupplierID	Supplier Name
1	Fabrikam
2	Tailspin Toys
3	Northwind Traders
4	Adventure Works
5	Lucerne Publishing
6	Proseware, Inc.
7	Contoso, Ltd
8	Wide World Importers
9	Wingtip Toys
10	Wide World Importers
11	Litware, Inc.
12	Southridge Video

The Products table

Product Name	SupplierID	CategoryID	Price
Programmer action figure	7	4	$12.95
Fun with C++	6	1	$15.85
Relational Database Diagram	5	2	$22.50
The Magical Computer Chip	3	2	$32.65
Access! The Game!	1	1	$22.95
Computer Geeks and Mythical Creatures	11	3	$78.50
Exercise for Computer Geeks! The DVD!	4	5	$14.88
Ultimate Flying Pizza	2	5	$36.75
External 5.25-inch Floppy Diskette Drive (1/4 Scale)	8	6	$65.00
Manager non-action figure	9	4	$78.88
Gloom	12	3	$53.33
Build Your Own Keyboard	1	6	$77.95

Note: The steps also assume a one-to-many relationship between the Categories and Products tables, and the same between the Suppliers and Products tables. In this case, the tables share the SupplierID and CategoryID fields. The totals query described in the next sections will not work without the relationships.

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.

Tip: If you don't want the heading of the Price column to appear as MaxOfPrice or MinOfPrice, open the query in Design view and, in the Price column in the grid, type Price: MaxOfPrice or Price: MinOfPrice. Doing so makes Price appear as the heading of the column in Datasheet view.

Top of Page

Find the records that contain top and bottom values

The queries that you created earlier in this article can return top or bottom values, but not both. 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.

The process of finding top and bottom values and displaying that data in a table follows these broad steps:

• Create a top values query and a bottom values query or, if you need to group your data, create totals queries that use the Min and Max functions.

• Covert your top values query (or your Max totals query) into a make table query and run this query to create a new table.

• Convert your bottom values query (or your Min totals query) into an append query and run this query to append the records to your top values table.

  The steps in these sections explain how to create the query.

  Note: Unless your database is digitally signed or it resides in a trusted location, Access prevents you from running the action queries described here. Follow the first set of steps to enable any blocked content before you try to create the make table and append queries described here.

  Enable the content in a database

  • On the Message Bar, click Enable Content.

  • Run your query again.

• Create the top and bottom values queries.

  For the steps needed to create a top or bottom values query, see the section Find the records with top or bottom values, earlier in this article. If you need to group your records by category, see the section Find the top or bottom values for records in categories or groups, earlier in this article.

• Save each query and leave open for use in the next steps.

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