Duplicate output in aggregate query (1 Viewer)

[MilaK]

Hello,

I’m making a query “Max210” that should return SampleDate, InterpP210, and Patient ID from Table “tbl_Samples” for the most recent Sample Date when InterP210 is Negative. I’m getting duplicate patient Ids in query output for some reason. The db is attached.

Could someone please take a look.

Here is the query:

SELECT T1.SampleDate, T1.InterpP210, T1.PatientID, (SELECT tbl_Samples.[ID] FROM tbl_Samples WHERE tbl_Samples.ID=T1.MAXOFID) AS MaxSample
FROM (SELECT tbl_Samples.SampleDate, tbl_Samples.InterpP210, tbl_Samples.PatientID, Max(tbl_Samples.ID) AS MaxOfID FROM tbl_Samples WHERE (((tbl_Samples.InterpP210)="Negative")) GROUP BY tbl_Samples.SampleDate, tbl_Samples.InterpP210, tbl_Samples.PatientID)  AS T1;

Thanks,

Mila

 

[plog]

Max210 should return SampleDate, InterpP210, and Patient ID from Table “tbl_Samples” for the most recent Sample Date when InterP210 is Negative

1. I see no criteria in your query for InterP210.

2. Your definition makes it logically possible for duplicates to occur.

3. Divide and conquer. I would do this in at least 2 queries.

The first query would identify the most recent date for each patient with InterP210 is negative. Below is pseudo code--I'm not using your field names:

SELECT PatientID, MAX(SampleDate) AS RecentSample
FROM YourTableNameHere
WHERE InterP210 = "Negative"
GROUP BY PatientID

Make it work for your database and name it "sub1". That does step 1 and identifies the latest date for each patient. To get the entire record you now create a new query. You use YourTableNameHere and sub1 in a new query--join the data sources appropriately, bring down all data from YourTableNameHere and apply the InterP210 criteria to it. That will get you the data you want.

Again, that does not guarantee non-duplicates because the logic you laid out does not. It will find every record that occurs on the most recent date for a pateint--if they had 2 samples on that date both those will show in the final query.