Find records that have the most or least recent dates

This article explains how to use top values queries and totals queries to find the most recent or earliest dates in a set of records. This can help you answer a variety of business questions, such as when a customer last placed an order, or which five quarters have been your best for sales, by city.

In this article

• Overview

• Prepare sample data to follow along with the examples

• Find the most or least recent date

• Find the most or least recent dates for groups of records

Overview

You can rank data and review the highest-ranked items by using a top values query. A top value query is a select query that returns a specified number or percent of values from the top of the results, for example, the five most popular pages on a web site. You can use a top values query against any kind of values – they don't have to be numbers.

If you want to group or summarize your data before you rank it, you don't have to use a top values query. 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.

When you use a top values query to find records that contain the latest or earliest dates in a table or group of records, you can answer a variety of business questions, such as the following:

• Who has been making the most sales lately?

• When did a customer last place an order?

• When are the next three birthdays on the team?

To make a top value query, start by creating a select query. Then, sort the data according to your question – whether you are looking for the top or the bottom. If you need to group or summarize the data, turn the select query into a totals query. You can 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.

This article assumes that the date values that you use have the Date/Time data type. If your date values are in a Text field, .

Consider using a filter instead of a top values query

A filter is usually better if you have a specific date in mind. To determine whether you should create a top values query or apply a filter, consider the following:

• If you want to return all the records where the date matches, is prior to, or later than a specific date, use a filter. For example, to see the dates for sales between April and July, you apply a filter.

• If you want to return a specified amount of records that have 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. For example, to see the five best sales quarters, use a top values query.

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

Top of Page

Prepare sample data to follow along with the examples

The steps in this article use the data in the following sample tables.

The Employees table   

LastName	First Name	Address	City	CountryOrR egion	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

The EventType 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/2011	$10,000
2	Corporate Function	Tailspin Toys	4/21/2011	$8,000
3	Trade Show	Tailspin Toys	5/1/2011	$25,000
4	Exhibit	Graphic Design Institute	5/13/2011	$4,500
5	Trade Show	Contoso, Ltd.	5/14/2011	$55,000
6	Concert	School of Fine Art	5/23/2011	$12,000
7	Product Launch	A. Datum	6/1/2011	$15,000
8	Product Launch	Wingtip Toys	6/18/2011	$21,000
9	Fund Raiser	Adventure Works	6/22/2011	$1,300
10	Lecture	Graphic Design Institute	6/25/2011	$2,450
11	Lecture	Contoso, Ltd.	7/4/2011	$3,800
12	Street Fair	Graphic Design Institute	7/4/2011	$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.

Paste the sample data into Excel worksheets

1. Start Excel. An empty workbook opens.

2. Press SHIFT+F11 to insert a worksheet (you will need four).

3. Copy the data from each sample table into an empty worksheet. Include the column headings (the first row).

Create database tables from the worksheets

1. Select the data from the first worksheet, including the column headings.

2. Right-click the Navigation Pane, and then click Paste.

3. Click Yes to confirm that the first row contains column headings.

4. Repeat steps 1-3 for each of the remaining worksheets.

Find the most or least recent date

The steps in this section use the sample data to illustrate the process of creating a top values query.

Create a basic top values query

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

2. Double-click the Employees table, and then click Close.

   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.

   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.

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

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

7. Save the query as NextBirthDays.

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

These steps use the query created in the preceding procedure. You can follow along with a different top values query as long as it contains actual Date/Time data, not text values.

Tip:  If you want to better understand how this query works, switch between Design view and Datasheet view at each step. If you want to see the actual query code, switch to SQL view. To switch between views, right-click the tab at the top of the query and then click the view that you want.

1. In the Navigation Pane, right-click the NextBirthDays query, and then click Design View.

2. In the query design grid, in the column to the right of BirthDate, enter the following:
   MonthBorn: DatePart("m",[BirthDate]).
   This expression extracts the month from BirthDate by using the DatePart function.

3. In the next column of the query design grid, enter the following:
   DayOfMonthBorn: DatePart("d",[BirthDate])
   This expression extracts the day of the month from BirthDate by using the DatePart function.

4. Clear the check boxes in the Show row for each of the two expressions you just entered.

5. Click the Sort row for each expression, and then select Ascending.

6. 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:

   • Month( [Birth Date]) > Month(Date()) specifies that the birth date of each employee falls in a future month.

   • The Month([Birth Date])= Month(Date()) And Day([Birth Date])>Day(Date()) specifies that if the birth date occurs in the current month, the birthday falls on or after the current day.

     In short, this expression excludes any records where the birthday occurs between January 1 and the current date.

     Tip:  For more examples of query criteria expressions, see the article Examples of query criteria.

7. On the Design tab, in the Query Setup group, type 3 in the Return box.

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

Note:  In your own query using your own data, you might sometimes see more records than you specified. If your data contains multiple records that share a value that is among the top values, your query will return all such records even if it means returning more records than you wanted.

Top of Page

Find the most or least recent dates for groups of records

You use a totals query to find the earliest or latest dates for records that fall into groups, such as events grouped by city. A totals query is a select query that uses aggregate functions (such as Group By, Min, Max, Count, First, and Last) to calculate values for each output field.

Include the field that you want to use for categories – to group by – and the field with values that you want to summarize. If you include other output fields – say, the names of customers when you are grouping by event type – the query will also use those fields to make groups, changing the results so that they don't answer your original question. To label the rows using other fields, you create an additional query that uses the totals query as a source, and add the additional fields to that query.

Tip:  Building queries in steps is a very effective strategy for answering more advanced questions. If you are having trouble getting a complicated query to work, consider whether you could break it down into a series of simpler queries.

Create a totals query

This procedure uses the Events sample table and the EventType sample table to answer this question:

When was the most recent event of each event type, excluding concerts?

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

2. Double-click the Events and EventType tables.
   Each table appears in the top section of the query designer.

3. Double-click the EventType field of the EventType table and the EventDate field from the Events table to add the fields to the query design grid.

4. In the query design grid, in the Criteria row of the EventType field, enter <>Concert.

   Tip:  For more examples of criteria expressions, see the article Examples of query criteria.

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

6. In the query design grid, click the Total row of the EventDate field and then click Max.

7. On the Design tab, in the Results group, click View, and then click SQL View.

8. In the SQL window, at the end of the SELECT clause, just after the AS keyword, replace MaxOfEventDate with MostRecent.

9. Save the query as MostRecentEventByType.

Create a second query to add more data

This procedure uses the MostRecentEventByType query from the preceding procedure to answer this question:

Who was the customer at the most recent event of each event type?

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

2. On the Queries tab, double-click the MostRecentEventByType query.

3. On the Tables tab, double-click the Events table and the Customers table.

4. In the query designer, double-click the following fields:

   1. On the Events table, double-click EventType.

   2. On the MostRecentEventByType query, double-click MostRecent.

   3. On the Customers table, double-click Company.

5. In the query design grid, in the Sort row of the EventType column, select Ascending.

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

Top of Page