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

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 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 FirstName Address City CountryOrRegion BirthDate HireDate
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 EventType
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 EventType Customer EventDate 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.

The Show Table dialog box appears.

  1. Double-click the Employees 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 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.

  1. On the Design tab, in the Query Setup group, type 3 in the Return box.
  2. On the Design tab, in the Results group, click Run Button image.

 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 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. In the Show Table dialog box, double-click the Events and EventType tables.
    Each table appears in the top section of the query designer.
  3. Close the Show Table dialog box.
  4. 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.
  5. 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.

  1. On the Design tab, in the Show/Hide group, click Totals.
  1. In the query design grid, click the Total row of the EventDate field and then click Max.
  2. On the Design tab, in the Results group, click View, and then click SQL View.
  3. In the SQL window, at the end of the SELECT clause, just after the AS keyword, replace MaxOfEventDate with MostRecent.
  4. 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. In the Show Table dialog box, on the Queries tab, double-click the MostRecentEventByType query.
  3. On the Tables tab of the dialog box, 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 Top of Page

Provided by

StevenT

Microsoft

 
 
Applies to:
Access 2010