Sign in with Microsoft
Sign in or create an account.
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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


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   


First Name



CountryOrR egion

Birth Date

Hire Date



1 Main St.

New York






52 1st St.







3122 75th Ave. S.W.






Jean Philippe

1 Contoso Blvd.







Calle Smith 2

Mexico City






3122 75th St. S.







67 Big St.







2 Nosey Pkwy





The EventType table    


Event Type


Product Launch


Corporate Function


Private Function


Fund Raiser


Trade Show








Street Fair

The Customers table    





Contoso, Ltd. Graphic

Jonathan Haas


Tailspin Toys

Ellen Adams



Carol Philips


Wingtip Toys

Lucio Iallo


A. Datum

Mandar Samant


Adventure Works

Brian Burke


Design Institute

Jaka Stele


School of Fine Art

Milena Duomanova

The Events table    


Event Type


Event Date



Product Launch

Contoso, Ltd.




Corporate Function

Tailspin Toys




Trade Show

Tailspin Toys





Graphic Design Institute




Trade Show

Contoso, Ltd.





School of Fine Art




Product Launch

A. Datum




Product Launch

Wingtip Toys




Fund Raiser

Adventure Works





Graphic Design Institute





Contoso, Ltd.




Street Fair

Graphic Design Institute



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 Button imageto 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 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

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

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?

Thank you for your feedback!