Find records that have the most or least recent dates

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

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.

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.

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 .

Top of Page

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