Others

Fix Max and First Aggregate Results in Microsoft Access 2016 Queries

Steve KSteve K Nov 20, 2026 868 views

Question details

Verify that Max and First aggregate functions return the intended results in an Access 2016 query.

Fix Max and First Aggregate Results in Access 2016 Queries
Product
Microsoft Access 2016
Device & OS
not provided
Scenario
The user wants to group records and check aggregate results, preferably using Query Design View.
Observed behavior
The user needs a way to test Max and First results and determine whether the query returns the expected values.
Before you start

Identify the table and fields to group, and check the value field's data type in Table Design. Prepare a few test records with repeated names and different values so you can verify the results.

Solution 1Recommended

Test Max by group in Access Query Design

Use the design grid to check the highest value for each name without writing a full SQL query.

1
Create a query

In Access 2016, select Create > Query Design, add the table containing the records, and close the Show Table window.

2
Add the fields

Double-click the name field and the value field to add them to the query design grid.

3
Set the aggregate

On the Design tab, select Totals. Leave the name field set to Group By and change the value field's Total row to Max.

4
Run and compare

Select Run and compare each result with the source records. For example, a name with numeric values 3 and 7 should return 7.

5
Inspect the SQL

To compare the generated query with an example, switch to View > SQL View. An equivalent query is: SELECT RecordName, Max(RecordValue) AS MaxOfRecordValue FROM Table1 GROUP BY RecordName; Replace the example table and field names with your own.

Test Max by group in Access Query Design
Check the data type: Max on a numeric field returns the highest number. If numbers are stored as text, Max compares text values instead, which may produce an unexpected result.
Free Microsoft Office alternative

Review Tabular Data with WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with Office documents and spreadsheets. It can help you review exported tabular data, but it does not fix Access queries or replace an Access database.

Review exported query results in a spreadsheetOpen and work with Microsoft Excel XLS and XLSX filesUse a familiar interface for an easier transition from OfficeWork with documents and spreadsheets in a lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does First not return the earliest record?

First returns a value from the first record encountered, not reliably the earliest by date. In Query Design, use Totals and set the date field to Min when you need the earliest date for each group.

How can I see the SQL behind my Access query?

Open the query in Design View and select View > SQL View. You can inspect or copy the generated SQL statement there.

Are Max and DMax the same in Access?

No. Max is an aggregate used in a query, often with Group By. DMax is a domain aggregate that evaluates a field over a specified table or query domain and can include criteria.

What should I provide if my aggregate query still returns the wrong result?

Share representative sample records, the query from SQL View, the actual output, and the result you expected. Include field data types if Max appears to compare values unexpectedly.