Find records with the most recent or oldest dates

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 Short 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 filters, see the article Apply a filter to view select records in an Access database.

Top of Page

Create a basic top values query

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

2. 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 listed is the previous section, 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 Query Design tab, in the Query Setup 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 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

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.

3. Switch to Design view.

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

5. Switch to Design view.

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

7. Click Run.

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

9. On the Query Design tab, in the Query Setup 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.

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

Create the totals query

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

2. Double-click the tables that you want to use. 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.

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

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

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

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

   The Totals row appears in the design grid.

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

7. On the Query Design tab, in the Query Setup 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.

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

2. Click the Queries tab, and then double-click the totals query that you created in the previous section.

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

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

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

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

7. On the Query Design tab, in the Results group, click Run.

   The results of the query are displayed in Datasheet view.

Top of Page

Create the make table query

1. With your top values query open in Design view:

   On the Query Design tab, in the Query Type group, click Make Table.

   The Make Table dialog box appears.

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

3. Save and close the query.

Create an append query

1. With your bottom value query in Design view:

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

2. The Append dialog box appears.

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

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

This action or event has been blocked by Disabled Mode.

If you see that message, take the following steps:

• Select Enable this content and then click OK.

• Run your query again.

Top of Page