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.

Access select MAX record 01

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.

Access select MAX record 02

 

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

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *