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!
Advertisement
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;