how to get a record based on max date for each user in different multiple tables

I’m trying to make a query that can select the names, DateOfSpecimenResult,SwabResult,DateOfReleaseOfResult. Heres the Visual Representation:

Table: ContactTracingHeader

| AutoID |   Name  | 
    1       Jason
    2       Chris

Table: Swab

| PatientNo | DateOfSpecimenCollection | SwabResultID | DateOfReleaseOfResult |
      1             05/02/2020                1               05/10/2020
      1             06/08/2020                1               06/11/2020
      1             07/16/2020                2               07/20/2020

Note: ContactTracingHeader.AutoID = Swab.PatientNo

Table: SwabResult

| AutoID | SwabResult |
     1      POSITIVE
     2      NEGATIVE

Query Output that I’m trying to make

| AutoID |  Name  | DateOfSpecimenCollection | SwabResult | DateOfReleaseOfResult |
     1     Jason          07/16/2020            NEGATIVE          07/20/2020
     2     Chris          (BLANK)               (BLANK)            (BLANK)

Note: Swab.ResultID = SwabResult.AutoID

Here, I’m only trying to show a Name with the latest DateOfSpecimenCollection and then use it as a reference for the 2 other column which is SwabResult and DateOfReleaseOfResult, And Since “Chris” doesn’t have a input on the Swab table, his records are blank but his name and AutoID Still appears on the table. The SwabResult shows the NEGATIVE, or POSITIVE depending on the ID from table Swab.

What i have done so far is this:

SELECT
CTH.AutoID, 
CTH.Firstname, 
CTH.Lastname, 

(SELECT MAX(DateOfSpecimenCollection)
            FROM Swab
            WHERE Swab.PatientNo = CTH.AutoID
        ) AS DateOfSpecimenCollection,

Swab.SwabResultID,

Swab.DateOfReleaseOfResult

FROM ContactTracingHeader AS CTH LEFT JOIN Swab ON Swab.PatientNo = CTH.AutoID;

This Query gets the latest DateOfSpecimenCollection and Output those who aren’t in the Swab table which is correct, however it duplicates the record depending how many SwabResult or DateOfReleaseOfResult it has. Also I tried INNER JOIN the table SwabResult so i can output the SwabResult instead of the ID, However it gave me JOIN Expression not supported Error.

I apologize for the long and confusing explanation and duplicate question as I’m trying to strugge to find some answers on the internet. Thank you!

Answer

Need a unique identifier in Swab table. If not already there, autonumber should serve

Consider:

Query1:

SELECT Swab.* FROM Swab WHERE ID IN 
   (SELECT TOP 1 ID FROM Swab AS Dupe WHERE Dupe.PatientNo=Swab.PatientNo 
    ORDER BY Dupe.DateOfSpecimenCollection DESC);

Query2

SELECT ContactTracingHeader.AutoID, ContactTracingHeader.Name, Query1.DateOfSpecimenCollection, 
SwabResult.SwabResult, Query1.DateOfReleaseOfResult
FROM ContactTracingHeader LEFT JOIN (SwabResult RIGHT JOIN Query1 
ON SwabResult.AutoID = Query1.SwabResultID) 
ON ContactTracingHeader.AutoID = Query1.PatientNo;

Or this sequence:

Query1:

SELECT Swab.PatientNo, Max(Swab.DateOfSpecimenCollection) AS MaxOfDateOfSpecimenCollection
FROM Swab
GROUP BY Swab.PatientNo;

Query2:

SELECT Swab.PatientNo, Swab.DateOfSpecimenCollection, Swab.SwabResultID, 
Swab.DateOfReleaseOfResult
FROM Query1 INNER JOIN Swab 
ON (Query1.MaxOfDateOfSpecimenCollection = Swab.DateOfSpecimenCollection) 
AND (Query1.PatientNo = Swab.PatientNo);

Query3:

SELECT ContactTracingHeader.AutoID, ContactTracingHeader.Name, 
Query2.DateOfSpecimenCollection, SwabResult.SwabResult, Query2.DateOfReleaseOfResult
FROM SwabResult RIGHT JOIN (ContactTracingHeader LEFT JOIN Query2 
ON ContactTracingHeader.AutoID = Query2.PatientNo) 
ON SwabResult.AutoID = Query2.SwabResultID;