Others

How to Fix Incorrect First and Last Values in Grouped Access Queries

Adam Davis Oct 13, 2026 868 views

Question details

The user needs to retrieve correct values from the earliest or latest records in grouped database queries without getting unpredictable results.

How to Fix Incorrect First and Last Values in Grouped Access Queries

Product: Microsoft Access
Device & OS: not provided
Scenario: Grouping student records in a query to accurately find the earliest or latest status and dates.
Observed behavior: The First and Last aggregate functions return status and date values from incorrect records, even when the source query is ordered.

Before you start

Ensure that your source table contains a reliable primary key or a unique Date/Time field that can be used to accurately identify the earliest and latest records.

Solution 1Recommended

Use Min or Max Aggregate Functions with a Table Join

Avoid the unreliable First and Last functions by using Min or Max to find the specific date or ID, then join it back to the original table to retrieve other fields.

The First and Last functions in Access merely return the first or last record entered chronologically in the physical table, which often ignores your query sorting. Using Min or Max on a date or ID field is the most reliable way to identify correct boundary records.

Create a Totals Query

Open your database, navigate to the 'Create' tab on the ribbon, and click 'Query Design'. Add your primary source table.

Apply Min or Max

Click the 'Totals' button (Sigma icon) on the ribbon. Group by your main identifier (e.g., Student ID) and set the Date field's Total row to 'Max' (for latest) or 'Min' (for earliest). Save the query.

Create a Join Query

Create a new Select Query. Add both the original source table and the Totals query you just saved to the query design workspace.

Extract Matching Fields

Join the two tables by dragging the Student ID and Date fields from one table to the corresponding fields in the other. Add the required fields (like Status) to the grid to reliably retrieve the matching records.

Use Min or Max Aggregate Functions with a Table Join

Solution 2

Implement a Correlated Subquery

Use a SQL subquery to dynamically fetch the most recent or oldest record directly within your main query.

Free Microsoft Office alternative

Switch to WPS Office for Powerful Data Management

While Microsoft Access handles complex relational databases, WPS Spreadsheet offers an excellent, lightweight alternative for analyzing, grouping, and managing tabular data without writing complex SQL queries. It is fully compatible with Microsoft Excel formats and provides intuitive sorting, filtering, and Pivot Tables.

Free and lightweight Office suiteFully compatible with Microsoft Excel (.xlsx) formatsEasy-to-use Pivot Tables for reliable data groupingNo complex SQL queries required for basic data analysisFamiliar user interface for seamless migration

Frequently Asked Questions

Why do the First and Last functions return wrong data in Access?

In Microsoft Access, First and Last return the values from the first or last record based on the physical order they were stored in the database disk, not based on the sorting you apply in your query. This makes them highly unreliable for ordered datasets.

Can I use Min and Max for text fields in Access?

Yes, Min and Max will return the first and last values alphabetically when applied to text fields. However, if you want the chronologically latest record, you should apply Max to a Date/Time or AutoNumber field instead.

How do I group by multiple fields without getting duplicate results?

When designing your Totals query, ensure you only include the fields you strictly need for grouping (like Student ID) and the single field you are evaluating with Min or Max. Adding extra fields like 'Status' to the Totals query and setting them to 'Group By' will split your groups and cause duplicate rows.

Office Quick Solutions