Top 3 report

[LisaK]

I have a query that has multiple records on a patient. I need to only take
the Top 3 using the date field RecInsDt. I'm a pretty basic access user so
if anyone can give me ideas on how to do this that would be great. Here is
my query that I am using.

SELECT Test1.PatientID, Test1.Program, Test1.RecInsDt, Test1.ENGMT_CLS_RSN_DS
FROM Test1;

Thanks.

 

[Jerry Whittle]

SELECT TOP 3 Test1.PatientID,
Test1.Program,
Test1.RecInsDt,
Test1.ENGMT_CLS_RSN_DS
FROM Test1
ORDER BY Test1.RecInsDt DESC;

The above will get you the top 3 records in the table based on the date
field in descending order. If there is a tie for 3rd place, all the ties will
also show up.

 

[fredg]

I have a query that has multiple records on a patient. I need to only take
the Top 3 using the date field RecInsDt. I'm a pretty basic access user so
if anyone can give me ideas on how to do this that would be great. Here is
my query that I am using.

SELECT Test1.PatientID, Test1.Program, Test1.RecInsDt, Test1.ENGMT_CLS_RSN_DS
FROM Test1;

Thanks.

By Top3 I assume you mean by the latest dates.

If you look at the QBE grid when designing your query you will see the
"Topp Values" tool button which by default says "All".
Simply enter the value wanted in that tool button, in your case a 3.
Sort (Ascending or Descending) as needed according to the field you
wish to get the top 3 records from.

The resulting SQL should look like this.

SELECT Top3 Test1.PatientID, Test1.Program, Test1.RecInsDt,
Test1.ENGMT_CLS_RSN_DS FROM Test1 Order by Test1.RecInsDt Desc;

 

[LisaK]

I want all 1000 patients to show up but only the last 3 dates for each
patient. When I used this query it only showed the top 3 patients.

 

[Douglas J. Steele]

Check what Allen Browne has at
http://www.allenbrowne.com/subquery-01.html#TopN

 

[John Spencer]

SELECT Test1.PatientID, Test1.Program, Test1.RecInsDt, Test1.ENGMT_CLS_RSN_DS
FROM Test1
WHERE Test1.RecInsDt IN
(SELECT TOP 3 TEMP.RecInsDt
FROM Test1 as TEMP
WHERE Temp.PatientID = Test1.PatientID
ORDER BY Temp.RecInsDt Desc)

That should give you the latest three for each patient. It assumes that you
don't have Test1.RecInsDt duplicated for a patient. In that case you could
get multiple records on the same date if there were ties for 2nd place.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[LisaK]

That did it. Thanks Alot.

SELECT Test1.PatientID, Test1.Program, Test1.RecInsDt, Test1.ENGMT_CLS_RSN_DS
FROM Test1
WHERE Test1.RecInsDt IN
(SELECT TOP 3 TEMP.RecInsDt
FROM Test1 as TEMP
WHERE Temp.PatientID = Test1.PatientID
ORDER BY Temp.RecInsDt Desc)

That should give you the latest three for each patient. It assumes that you
don't have Test1.RecInsDt duplicated for a patient. In that case you could
get multiple records on the same date if there were ties for 2nd place.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County