Find records with the most recent or oldest dates

This article explains how to use a top value query to find the most recent or earliest dates in a set of records. You can use the information that this type of query returns to answer a variety of business questions, such as when a customer last placed an order.

What do you want to do?


Understand how top value queries work with dates

You use a top value query when you need to find records that contain the latest or earliest dates in a table or group of records. You can then use the data to answer several types of business questions, such as:

  • When did an employee last make a sale? The answer can help you identify a most-productive or least-productive employee.
  • When did a customer last place an order? If a customer has not placed an order for a given period of time, you may want to move the customer to an inactive list.
  • Who has the next birthday, or the next birthdays?

Rules for creating and using top values queries

You create a top value query by first creating a select query. Depending on the results that you want, you apply either a sort order to the query, or you convert the query into a totals query. If you convert the query, you then use an aggregate function, such as Max or Min to return the highest or lowest value, or First or Last to return the earliest or latest date. You use totals queries and aggregate functions only when you need to find data that falls into a set of groups or categories. For example, suppose that you need to find the sales numbers for a given date for each city in which your company operates. In that case, the cities become categories (you need to find the data per city), so you use a totals query.

As you proceed, remember that, regardless of query type, your queries must use fields that contain descriptive data, such as customer names, and also a field that contains the date values that you want to find. In addition, the date values must reside in a field set to the Date/Time data type. The queries that this article describes fail if you try to run them against date values in a Text field. In addition, if you want to use a totals query, your data fields must include category information, such as a city or country/region field.

Choosing between a top values query and a filter

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

  • If you want to return the records with the most recent or latest dates in a field, and you do not know the exact date values, or they don't matter, you create a top values query.
  • If you want to return all the records where the date matches, is prior to, or later than a specific date, you use a filter. For example, to see the dates for sales between April and July, you apply a filter. A complete discussion of filters is beyond this topic.

For more information about creating and using them, see the article Filter: Limit the number of records in a view.

Top of Page Top of Page

Find the most recent or earliest date

The steps in this section explain how to create a basic top values query that uses a sort order, plus a more advanced query that uses expressions and other criteria. The first section demonstrates the basic steps in creating a top values query, and the second section explains how to find the next few employee birthdays by adding criteria. The steps use the data in 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
Pica Guido 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

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

ShowShow me how

Enter the sample data manually

  1. On the Create tab, in the Tables group, click Table.

Access adds a new, blank table to your database.

 Note   You do not need to follow this step if you open a new, blank database, but you will need to follow it whenever you need to add a table to the database.

  1. Double-click the first cell in the header row and type the name of the field in the sample table.

By default, Access denotes blank fields in the header row with the text Add New Field, like so:

A new field in a datasheet

  1. Use the arrow keys to move to the next blank header cell, and then type the second field name (you can also press TAB or double-click the new cell). Repeat this step until you enter all field names.
  2. Enter the data in the sample table.

As you enter the data, Access infers a data type for each field. If you are new to relational databases, you should set a specific data type, such as Number, Text, or Date/Time, for each of the fields in your tables. Setting data types helps ensure accurate data entry and helps to prevent mistakes, such as using a telephone number in a calculation. For these sample tables, you should let Access infer the data type.

  1. When you finish entering the data, click Save

Keyboard shortcut  Press CTRL+S.

The Save As dialog box appears.

  1. In the Table Name box, type the name of the sample table, and then click OK.

You use the name of each sample table because the queries in the how-to sections use those names.

  1. Repeat these steps until you create each of the sample tables listed at the beginning of this section.

Copy the table to your spreadsheet program

  1. Start your spreadsheet program and create a new, blank file. If you use Excel, a new, blank workbook is created by default.
  2. Copy the sample table provided in the previous section and paste it into the first worksheet, starting at the first cell.
  3. Right-click the sheet tab, click Rename, and name the worksheet Employees.
  4. Save the spreadsheet file to a convenient location and go to the next steps.

Import the table into Access

  1. In a new or existing database:

On the External Data tab, in the Import group, click Excel.

-or-

Click More, and then select a spreadsheet program from the list.

The Get External Data - Spreadsheet dialog box appears.

  1. Click Browse, open the spreadsheet file that you created in the previous steps, and then click OK.

The Import Spreadsheet Wizard starts.

  1. By default, the wizard selects the first worksheet in the workbook (Employees, if you followed the steps in the previous section), and data from the worksheet appears in the lower section of the screen. Click Next.
  2. Click First Row Contains Column Headings, and then click Next.
  3. Optionally, use the text boxes and lists under Field Options to change field names and data types, or omit fields from the import process. Otherwise, click Next.

If you use the sample table above, you do not need to make any changes at this point.

  1. Leave the Let Access add primary key option selected, and click Next.
  2. By default, Access applies the name of the worksheet to your new table. Accept the name or enter another name, and then click Finish.

 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 Other group, click Query Design.

The Show Table dialog box appears.

  1. In the dialog box, click the table that you want to use in the query, click Add to place the table in the upper section of the designer, and then click Close.

-or-

Double-click the table, and then click Close.

If you use the sample data, add the Employees table to the query.

  1. 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.

  1. 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.

Descending sort order returns the most recent date, and Ascending sort order returns the earliest date.

 Important   You must set a value in the Sort row only for fields that contain your dates. If you specify a sort order for another field, the query does not return the results you want.

  1. On the Design tab, in the Tools group, click the down arrow next to All (the Top Values list), and either enter the number of records that you want to see, or select an option from the list.
  1. Click Run Button image to run the query and display the results in Datasheet view.
  2. 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. Switch the query that you created in the previous steps to Design view.
  2. In the query design grid, in the column to the right of the Birth Date column, copy and paste or type this expression: Expr1: DatePart("m",[Birth Date]). Then click Run.

The DatePart function extracts the month portion of the Birth Date field.

  1. Switch to Design view.
  2. To the right of your first expression, paste or type this expression: Expr2: DatePart("d",[Birth Date]). Then click Run.

In this case, the DatePart function extracts the date portion of the Birth Date field.

  1. Switch to Design view.
  2. Clear the check boxes in the Show row for each of the two expressions you just entered, click the Sort row for each expression, and then select Ascending.
  3. Click Run.
  4. 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, switch to Design view. Then, in the Criteria row of the Birth Date column, type the following 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([Birth Date]) > 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([Birth Date])= Month(Date()) And Day([Birth Date])>Day(Date()) portion of the expression checks the birth dates that occur in the current month to see if the birthday falls on or after the current day. If that condition is true, the function includes those records in the query. To summarize: This expression ignores any records where the birthday 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.

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

To see the next three birthdays, type 3.

  1. Click Run Button image to run the query and display the results in Datasheet view.

If you see more records than you specify

If your data contains multiple records that share a date value, your top values query may return more data than you specify. For example, you can design a top values query to retrieve three employee records, but the query returns four records because "Wilson" and "Edwards" share a birthday, as shown in the following table.

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

If you see fewer records than you specify

Suppose you design a query to return the top or bottom five records in a field, but the query returns only three records. As a rule, you solve that type of problem by opening the query in Design view and reviewing the Criteria row of the columns in the design grid.

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, the following table shows the results of a query that returns the five orders that were shipped most recently, along with the name of the salesperson who handled the transaction.

ShippedDate Salesperson
11/12/2004 Freitag
11/12/2004 Cajhen
10/12/2004 Delgado
10/12/2004 Delgado
10/12/2004 Freitag

The third and fourth records appear to be duplicates, but it is possible the salesperson Delgado 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, right-click anywhere in the blank area in the top half of the query designer, and then click Properties on the shortcut menu. In 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, hide, or eliminate duplicate data.

Top of Page Top of Page

Find the latest or earliest dates for records in categories or groups

You use a totals query to find the earliest or latest dates for records that fall into groups or categories. A totals query is a select query that uses aggregate functions such as Min, Max, Sum, First, and Last to calculate values for a given field.

The steps in this section assume that you run an event-management business — you take care of staging, lighting, catering, and the other facets of large functions. In addition, the events that you manage fall into several categories, such as product launches, street fairs, and concerts. The steps in this section explain how to answer a common question: When is the next event, by category? In other words, when is the next product launch, the next concert, and so on.

As you proceed, remember the following: by default, the type of totals query that you create here can include only the field that contains your group or category data and the field that contains your dates. You cannot include other fields that describe the items in a category, such as customer or supplier names. However, you can create a second query that combines your totals query with the fields that contain descriptive data. Steps later in this section explain how to do that task.

The steps in this section assume the use of the following three tables:

The Event Type table   

TypeID Event Type
1 Product Launch
2 Corporate Function
3 Private Function
4 Fund Raiser
5 Trade Show
6 Lecture
7 Concert
8 Exhibit
9 Street Fair

The Customers table   

CustomerID Company Contact
1 Contoso, Ltd. Graphic Jonathan Haas
2 Tailspin Toys Ellen Adams
3 Fabrikam Carol Philips
4 Wingtip Toys Lucio Iallo
5 A. Datum Mandar Samant
6 Adventure Works Brian Burke
7 Design Institute Jaka Stele
8 School of Fine Art Milena Duomanova

The Events table   

EventID Event Type Customer Event Date Price
1 Product Launch Contoso, Ltd. 4/14/2003 $10,000
2 Corporate Function Tailspin Toys 4/21/2003 $8,000
3 Trade Show Tailspin Toys 5/1/2003 $25,000
4 Exhibit Graphic Design Institute 5/13/2003 $4,500
5 Trade Show Contoso, Ltd. 5/14/2003 $55,000
6 Concert School of Fine Art 5/23/2003 $12,000
7 Product Launch A. Datum 6/1/2003 $15,000
8 Product Launch Wingtip Toys 6/18/2003 $21,000
9 Fund Raiser Adventure Works 6/22/2003 $1,300
10 Lecture Graphic Design Institute 6/25/2003 $2,450
11 Lecture Contoso, Ltd. 7/4/2003 $3,800
12 Street Fair Graphic Design Institute 7/4/2003 $5,500

 Note   The steps in this section assume that the Customers and Event Type tables reside on the "one" side of one-to-many relationships with the Events table. In this case, the Events table shares the CustomerID and TypeID fields. The totals queries described in the next sections will not work without those relationships.

ShowHow do I add this data to a database?

To add these sample tables to a database, you can follow the steps in the earlier section, Find records with top or bottom values, but with a few exceptions:

  • When you copy the Event Types and Customers tables to Excel, do not copy the TypeID and CustomerID columns. Access will add a primary key values for you when you import the worksheets; which saves you some time.
  • After you import the tables, you must open the Events table in Design view and convert the Event Type and Customer columns into lookup fields. To so, click the Data Type column for each field, and then click Lookup Wizard.

As part of creating the lookup fields, Access replaces the text values in the Event Type and Customer columns with numeric values from the source tables.

For more information about creating and using lookup fields, see the articles Use a list that stores multiple values and Guide to multivalued fields. Both articles explain how to create a type of lookup field that allows you to select multiple values for a given field and also explain how to create lookup lists.

Create the totals query

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

The Show Table dialog box appears.

  1. In the dialog box, select the tables that you want to work with, click Add, and then click Close when you finish adding the tables.

-or-

Double-click the tables that you want to use, and then click Close. Each table appears in the upper section of the query designer.

If you use the sample tables listed above, add the Events and Event Types tables.

  1. Double-click the table fields that you want to use in your query. Ensure that you add only the group or category fields and the value field to the query at this point.

If you use the sample data listed in the three preceding tables, you add either the Event Type field from the Event Type table, or the Event Date field from the Events table.

  1. Optionally, you can specify a criteria that limits the scope of the query. Only records that meet the criteria are sorted, and top and bottom field values are identified within this sorted list.

For example, if you want to return events in the Private Function category, you type this expression in the Criteria row of the Event Type column: <>"Private Function".

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

  1. Convert the query to a totals query by doing the following:

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

The Totals row appears in the design grid.

  1. Ensure that the Totals row of each group or category field is set to Group By, and then set the Totals row of the value field (the field with the top or bottom values) 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.

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

In this case, select All, and then click Run to display the results in Datasheet view.

 Note   Depending on the function that you chose in step 6, 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 MaxOfEvent Date or MinOfEvent Date.

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

The query results do not show 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 do this.

Create a second query to add more data

  1. On the Create tab, in the Other group, click Query Design.
  1. In the Show Table dialog box, click the Queries tab, and then double-click the totals query that you created in the previous section.
  2. Click the Tables tab and add the tables that you used in your totals query, and also add any tables that contain other related data. If you use the preceding three sample tables, add the Event Type, Event, and Customers tables to your new query.
  3. 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 from the three tables, drag the Event Type column in the totals query to the Event Type field in the Event Type table. You then drag the MaxOfEvent Date column in the totals query to the Event Date field in the Events table. Creating these joins enables your new select query to bring together the data in the totals query and the data in the other tables.

  1. Add the additional descriptive fields from the other tables in the query.

If you use the sample data from the three tables, you can add the Company and Contact fields from the Customers table.

  1. Optionally, specify a sort order for one or more of the columns. For example, to list the categories in alphabetical order, set the Sort row of the Event Type column to Ascending.
  2. On the Design tab, in the Results group, click Run.

The results of the query are displayed 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. Price will appear as the heading of the column in Datasheet view.

Top of Page Top of Page

Find the most recent and the earliest dates

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 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 create a new table.
  • Convert your bottom values query (or your Min totals query) into an append query and append the records to your top values table.

The steps in these sections explain how to do this.

Create the queries

  1. Create the top and bottom values queries.

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

If you use the sample tables from the last section, use only the data in the Events table. Use the Event Type, Customer, and Event Date fields from the Events table in both queries.

  1. Save each query with a meaningful name, such as Top Value and Bottom Value, and leave them 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.

  1. 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.

  1. Save and close the query.

Create an append query

  1. With your bottom value query in Design view:

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

  1. The Append dialog box appears.
  2. 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.

  1. 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 when Access prompts you. Then double-click the Bottom Value query and click Yes when Access prompts you.
  • Open the Top and Bottom Records table in Datasheet view.

 Important   If you try to run the make-table or append queries and it seems like nothing happens, check the Access status bar for the following message:

This action or event has been blocked by Disabled Mode.

If you see that message, take the following steps:

  • If it isn't already visible, show the Message Bar. To do so, on the Database Tools tab, in the Show/Hide group, click Message Bar.
  • On the Message Bar, click Options.

The Microsoft Office Security Options dialog box appears.

  • Select Enable this content and then click OK.
  • Run your query again.

For more information about Disabled mode and Access security, see the article Secure an Access 2007 database.

Top of Page Top of Page

 
 
Applies to:
Access 2007