Create a Top values query to find the highest values in set of unsorted records
Our database has a table named Show
Let’s find the students who have the highest grades. Solution:SELECT id, first_name, last_name, grade FROM student WHERE grade = (SELECT MAX(grade) FROM student); Here’s the result: idfirst_namelast_namegrade2GaryLarry55EllieBlack5Discussion:To find the maximum value of a column, use the Return any 10 rows from the SALES table. Because no ORDER BY clause is specified, the set of rows that this query returns is unpredictable.
The following query is functionally equivalent, but uses a LIMIT clause instead of a TOP clause:
Return the first 10 rows from the SALES table, ordered by the QTYSOLD column in descending order.
Return the first two QTYSOLD and SELLERID values from the SALES table, ordered by the QTYSOLD column: 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 queriesYou 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.
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:
Choosing between a top values query and a filterTo determine whether you should create a top values query or apply a filter, consider the following:
Rules for creating and using top values queriesAs 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 valuesThe 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
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 queryNote: These steps assume that you will use the query described in the previous section.
If your query returns more records than you expectIf 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 expectSuppose 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 recordsIf 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 groupsYou 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
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
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 valuesThe 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:
|