Access Filtering Question

[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

[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])

[richlocus]

Hans:
Thanks! Indeed I can translate it!
Rich