Fix Max and First Aggregate Results in Microsoft Access 2016 Queries
Question details
Verify that Max and First aggregate functions return the intended results in an Access 2016 query.

- 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.
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.
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.
In Access 2016, select Create > Query Design, add the table containing the records, and close the Show Table window.
Double-click the name field and the value field to add them to the query design grid.
On the Design tab, select Totals. Leave the name field set to Group By and change the value field's Total row to Max.
Select Run and compare each result with the source records. For example, a name with numeric values 3 and 7 should return 7.
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.

Check what First means in an Access totals query
Use this check when First returns a value other than the earliest record you expected.
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.

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.




