Access Query – Returning Non-duplicated and Specific Duplicate Records

I am running into walls at the moment trying to write statements that would return specific records in a table that may have some duplicated unique value (Employee ID). Please see below for example: (The table below was imported using a saved import and all fields are in ShortText data type and there’s no PK or indexes setup when imported)

EMPID	ActNumber	Status	ReqDate	LastDateChange
123	185236	Closed	02/01/2019	01/01/2020
123	210390	Open	01/01/2020
123	312395	Closed	01/01/2020
432	832102	Open	06/01/2020
313	921235	Closed	03/03/2020	05/01/2020
313	952305	Closed	03/03/2020
610	182349	Closed	11/01/2018

The requirement would be to create a report that returns the records using the following criteria in order:

1. Max(CDate([ReqDate]))
2. [Status]
3. If duplicate EMPID records found then take Open [Status]
4. If duplicate EMPID records found but only Closed [Status] then return the one where [LastDateChange] is not null

The only way I can think of doing this is through multiple statements:

• One to return the Max [ReqDate] with [Status] Open
• One to return the Max [ReqDate] with another statement looking at the duplicates with Closed [Status] And [LastDateChange] is not null
• Create a temp table that would take in the records from the above queries before adding to the final table

That’s the logic I was thinking of doing, but so far stuck on building it (feel like it’s too windy). The final result that I’m looking for would be the following output –

EMPID	ActNumber	Status	ReqDate	LastDateChange
123	210390	Open	01/01/2020
432	832102	Open	06/01/2020
313	921235	Closed	03/03/2020	05/01/2020
610	182349	Closed	11/01/2018

Any help would be wonderful and greatly appreciated, thank you!

Answer

UNION and NOT IN returns desired output for given sample data.

SELECT EmpID, ActNumber, Status, ReqDate, LastDateChange FROM tblTest 
    WHERE Status = "Open"
UNION SELECT EmpID, ActNumber, Status, ReqDate, LastDateChange FROM tblTest 
    WHERE NOT LastDateChange Is Null 
    AND EmpID NOT IN (SELECT EmpID FROM tblTest WHERE Status="Open")
UNION SELECT EmpID, ActNumber, Status, ReqDate, LastDateChange FROM tblTest 
    WHERE LastDateChange Is Null 
    AND EmpID NOT IN (SELECT EmpID FROM tblTest WHERE Status="Open") 
        AND EmpID NOT IN (SELECT EmpID FROM tblTest WHERE NOT LastDateChange Is Null 
        AND EmpID NOT IN (SELECT EmpID FROM tblTest WHERE Status="Open"))
;

Updated – KNog This is a revised of the above suggestion and I got the correct output.

SELECT tblTest.EmpID, ActNumber, Status, ReqDate, LastDateChange 
FROM tblTest 
INNER JOIN (
     SELECT EmpID, Max(CDate(ReqDate)) As MaxDate From tblTest WHERE Not EmpID Is Null AND Status = 'Open' GROUP BY EmpID) As MaxOpnAct
ON tblTest.EmpID = MaxOpnAct.EmpID
WHERE Status = "Open"

UNION SELECT tblTest.EmpID, ActNumber, Status, ReqDate, LastDateChange 
FROM tblTest
INNER JOIN (
     SELECT EmpID, Max(CDate(ReqDate)) As MaxDate From tblTest WHERE Not EmpID Is Null AND Status = 'Closed' GROUP BY EmpID HAVING Count(EmpID)=1) As MaxClsNonDup
ON tblTest.EmpID = MaxClsNonDup.EmpID

UNION SELECT tblTest.EmpID, ActNumber, Status, ReqDate, LastDateChange 
FROM tblTest
INNER JOIN (
     SELECT EmpID, Max(CDate(ReqDate)) As MaxDate From tblTest WHERE Not EmpID Is Null AND Status = 'Closed' GROUP BY EmpID HAVING Count(EmpID)>1) As MaxClsDup
ON tblTest.EmpID = MaxClsDup.EmpID
WHERE NOT LastDateChange Is Null
     AND Status = 'Closed'
     AND CDate(ReqDate) = MaxDate