MS Access select the first record of each group using First Function

This MS Access tutorial explains how to select the first record of each group using First Function.

MS Access select the first record of each group

When I create human resources report, I have always come across a situation where one employee has more than one records. For example, I want to create a staff list where one employee only has one row of record. If I add emergency contact field to the report and if an employee has more than one emergency contacts, then the Query will return one employees with multi-rows of record as below.

Student ID Name Emergency Contact
1 Apple Mary
1 Apple John
2 Banana Patrick
2 Banana Queenie
3 Cat Anthony

 

In order to avoid multiple records of each EE, we have to extract the first record of Emergency Contact for each employee as shown below.

Student ID Name Emergency Contact
1 Apple Mary
2 Banana Patrick
3 Cat Anthony

Although it is not ideal as the other Emergency Contact is lost in the staff list, it is still the best workaround we can have.

In this post, I will demonstrate how to do it in MS Access.

MS Access select the first record of each group – using First Function

First of all, Group by Student ID and Name (Home > totals icon), then in the Emergency Contact, select aggregate function First, it means after the data is grouped by Student ID, Name, the first record of each group is returned.

 

Then we get the below result.

Student ID Name Emergency Contact
1 Apple Mary
2 Banana Patrick
3 Cat Anthony

If you want to apply sorting before selecting the first record, you should create another Query in advance to sort Student ID and Emergency Contact, then the first Emergency Contact changed.

 

There is also another Function called Last, which select the last record of each group.

 

Result

Student ID Name Emergency ContactOfLast
1 Apple John
2 Banana Queenie
3 Cat Anthony

Instead of applying First and Last, you may also consider Min and Max Function. When applying Min and Max, the texts are compared in the same way as sorting text in Excel.

Outbound References

https://support.office.com/en-ie/article/first-last-functions-acd76019-c37f-432d-9807-4fc63cac1db5

Wyman W
Compensation Survey Manager@Mercer
Wyman is human resources professional, specialized in business analysis, project management, data transformation with Access and Excel.

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

You may drop me a message if you are interested in HR consultancy services from Mercer.

Leave a Reply

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