Set max to 3 to retrieve last 3 most recent dates

  • Thread starter Thread starter MS Access Scheduler Database
  • Start date Start date
M

MS Access Scheduler Database

I have a table X that contains person's blood pressure reading dates and
sys/dia results. Many may have more than 5 BP readings. I want to return
only the 3 most recent dates based on PersonID.

PersonID BPDate sys dia
1 4/3/2009 130 70
1 5/24/2009 145 80
2 4/22/2009 136 90
2 6/10/2009 136 90
2 9/23/2009 136 90
2 11/01/2009 136 90

Also to group the data by PersonID to show the BP reading as BPdate1,
BPDate2, BPdate3 and BP readings in a row. For example - result as:

Person ID BPDate1 sys1 dia1 DPdate2 sys2 dia2 so
on.....
1 4/3/2009 130 70 5/24/2009 145 80

I've been researching this for weeks - Please help!
 
Here's the first half. Hopefully the table is really named X.

SELECT X1.PersonID,
X1.BPDate,
X1.sys,
X1.dia
FROM X AS X1
WHERE X1.BPDate In
(SELECT TOP 3 X2.BPDate
FROM X AS X2
WHERE X2.PersonID=X1.PersonID
ORDER BY X2.BPDate DESC)
ORDER BY 1,2 DESC;
 
The easiest way to handle this in a report would be to use a subreport to show
the results. The main report would show the personId and not have any other
fields in it. Based on your table "X" the source for the main report would be

SELECT Distinct PersonID
FROM X

The sub-report would use the query that Jerry Whittle has proposed (without
the final Order By clause. Set up the subreport as a multi column Across then
down (3 columns) report with the three fields BPDate, Sys, and Dia.

If you really, really need to do this in a query then Jerry Whittle's proposal
is a start on the queries I can envision to do this and the performance will
probably be terrible.

The next query will rank the three (or less) records returned by Jerry
Whittle's query (call it qJW) and then you will join this query (qRanked) to
itself three times and use the rank to return record 1, 2 and 3 for each PersonID.

SELECT A.PersonID, A.BPDate, A.Sys, A.Dia, Count(B.BPDate) as Rank
FROM qJW as A LEFT JOIN qJW as B
ON A.PersonID = B.PersonID
AND A.BPDate<B.BPDate
GROUP BY A.PersonID, A.BPDate, A.Sys, A.Dia

Now use that query (qRanked) in a query that looks like the following:
SELECT A.PersonID, A.BPDate, A.Sys, A.Dia,
B.BPDate, B.Sys, B.Dia,
C.BPDate, C.Sys, C.Dia
FROM (qRanked as A LEFT JOIN qRanked as B
On A.PersonID = B.PersonID
AND A.Rank = B.Rank-1)
LEFT JOIN qRanked as C
On A.PersonID = C.PersonID
AND A.Rank = C.Rank-2

Please note that NONE of this speculation has been tested.

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