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