Access Filtering Question

richlocus
2StarLounger
Posts: 149
Joined: 03 Oct 2015, 00:30

Access Filtering Question

Post by richlocus »

Hello:
I am still trying a way to filter a data stream with certain characteristics.

Here's a hypothetical sample. An employee can have from one to many records, depending on their changing status.

EmployeeA Inactive 1/5/2021 Data1
EmployeeA Active 3/4/2022 Data2
EmployeeA Ineligible 4/1/2020 Data3

EmployeeB Inactive 5/2/2021 Data1

EmployeeC Inactive 4/1/2021Data1
EmployeeC Active 8/4/2022Data2

I want to choose the ONE record for each employee with the latest date, and keep all the information in that record (there are many other fields)

For example, I would choose these records:
EmployeeA Active 3/4/2022 Data2
EmployeeB Inactive 5/2/2021 Data1
EmployeeC Active 8/4/2022 Data2

Please provide an example, preferably in the query builder, but if not, then the SQL code (which I can convert to the query builder format).

Thanks!
Rich Locus

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Access Filtering Question

Post by HansV »

I hope you can translate this to your setup:

SELECT [TableName].* FROM [TableName] WHERE [TableName].[DateField] = (SELECT Max(T.[DateField]) FROM [TableName] AS T WHERE T.[EmployeeName] = [TableName].[EmployeeName])
Best wishes,
Hans

richlocus
2StarLounger
Posts: 149
Joined: 03 Oct 2015, 00:30

Re: Access Filtering Question

Post by richlocus »

Hans:
Thanks! Indeed I can translate it!
Rich