This MS Access tutorial explains how to use Access SQL to select MAX record (MAX row) of each group.
Access SQL select MAX record
In Microsoft Access, you can select fields you need to Group and then use MAX to get the max record of each Group. However, the result only returns the grouped fields but not all the fields in the original table. In this tutorial I will show two methods to select all fields.
Example – Select Max data
Table1 below stores the performance rating of each employee in different departments.
To find the employee of highest rating of each department, select only Department and Performance Rating field as below. Do not Group By Employee Name because it will make the query get the Max rating for each employee name within each department.
The below result returns the highest rating of each department.
In this example, we have only selected two fields but we are unable to select all the fields in the original table.
Access SQL select MAX record – Method 1
In order to show Max record of all the fields, create another new Query to join the result of Query1 back to Table1.
The below result shows Max rating with all fields.
Access SQL select MAX record – Method 2
Type the SQL directly in SQL View
SELECT Table1.* FROM Table1 WHERE Table1.Performance_Rating= (SELECT Max(Table2.Performance_Rating) From Table1 Table2 WHERE Table1.Department = Table2.Department Group by Table2.Department)
This method is similar to Method 1, but this method combines the subquery with the main query and defines which field to join the table. This method is more elegant than creating two queries and is useful when you need to write a single Query in Access VBA.