MS Access SQL select MAX record of each group

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.

Table1

Department	Employee_Name	Performance_Rating
IT	John	5
IT	Peter	4
HR	Mary	3
HR	May	2
HR	June	5
Finance	Frank	4
Finance	David	3

 

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.

Query1

Department	Performance_RatingOfMax
Finance	4
HR	5
IT	5

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.

Method 1

Department	Employee_Name	Performance_RatingOfMax
IT	John	5
HR	June	5
Finance	Frank	4

 

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.

 

Result

Method 2

Department	Employee_Name	Performance_Rating
IT	John	5
HR	June	5
Finance	Frank	4