Function Problems

How to Find the Latest Record ID for Each Group in Access Queries

Olivia Miller Oct 1, 2026 871 views

Question details

The user needs to retrieve the unique InjuryID for the most recent injury occurrence, grouped by ForemanEmployeeID and InjuryClassID, without returning duplicate records.

How to Find the Latest Record ID for Each Group in Access Queries

Product: Access / SQL Database
Device & OS: not provided
Scenario: Grouping records by specific fields and retrieving the unique identifier associated with the maximum date for each group.
Observed behavior: Returns the single most recent record ID for each foreman and injury class combination.

Before you start

Ensure you have a basic understanding of SQL syntax or the Query Design View in your database tool, and verify that your table contains the necessary ID, category, and date fields.

Solution 1Recommended

Use a Totals Query and Inner Join

This recommended method involves creating an intermediate query to find the maximum date for each group, then joining it back to the original table to retrieve the specific ID.

This approach is highly effective in Microsoft Access and other visual query builders. It separates the grouping logic from the data retrieval logic, making it easy to maintain.

Create a totals query

In your database tool, open the Query Design View. Add your target table (e.g., tblInjuries). Add the fields you want to group by (ForemanEmployeeID and InjuryClassID) and the date field (InjuryDate). Click the 'Totals' button on the ribbon, leave the group fields as 'Group By', and change the 'Total' row for the date field to 'Max'.

Save the totals query

Save this query with a descriptive name, such as 'qryMaxDate'.

Create a new retrieve query

Open a new Query Design View and add both your original table (tblInjuries) and your newly created totals query (qryMaxDate).

Join the queries

Click and drag to create Join lines connecting the grouped fields (ForemanEmployeeID, InjuryClassID) and the date field between the original table and the totals query.

Select the target ID

Double-click the InjuryID field from your original table to add it to the query grid. Run the query to display the ID of the latest record for each group.

Handling Date Ties: If multiple records share the exact same maximum date, the join will return all of them. To resolve this, apply a tie-breaking rule in your final query, such as selecting the Max(InjuryID).

Solution 2

Use a Correlated EXISTS Subquery

An alternative SQL-based method using the EXISTS clause to filter the main table directly without needing a separate saved query.

Free Microsoft Office alternative

Need to analyze data without complex SQL? Try WPS Spreadsheet

While complex relational database queries require specific software like Microsoft Access, you can handle extensive data grouping, filtering, and analysis effortlessly using PivotTables and advanced formulas in WPS Spreadsheet. It is a free, lightweight, and highly compatible alternative to Microsoft Office.

1. Export your database table: Export your database records (such as tblInjuries) to a .csv or .xlsx file.
2. Open in WPS Spreadsheet: Launch WPS Office and open the exported file in WPS Spreadsheet.
3. Create a PivotTable: Select your data range, navigate to the Insert tab, and click PivotTable to quickly group your data by Foreman and Injury Class while displaying the Max Date.

Free and lightweight complete Office suiteFully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Powerful data analysis with PivotTables and advanced grouping featuresFamiliar user interface for seamless migration and quick adoption

microsoft office alternative - wps office

Frequently Asked Questions

Why does my query return multiple records for the latest date?

If multiple injuries for the same foreman and injury class occurred on the exact same date, the table join will return all of those records. To resolve this, you must add a tie-breaking rule, such as grouping the final results again and returning the maximum InjuryID among the tied records.

What is a correlated subquery and how does it work?

A correlated subquery is a query nested inside another query that uses values from the outer query. It executes once for every row processed by the outer query. In this scenario, it checks if the current row's date matches the maximum date for its specific group, filtering the results dynamically.

Can I perform this grouping operation in a spreadsheet instead of a database?

Yes. If you export your data to a spreadsheet application like WPS Spreadsheet, you can achieve the same result without writing SQL. You can use PivotTables to find the maximum date for each group, or sort your data by date descending and use the Remove Duplicates feature based on the group columns.

Excel & Data Problems