Show only most recent records

[LMB]

Hi Everyone,

Access 2003. I am trying to get a query to show me only the records for
each patient that is the most recent based on the the
PtLocationStartDateAndTime field. Right now in my test database I have 2
patients who I show in 2 different rooms on different dates. I see all 4
records. I only want to see the record for the newest location. Here is
the sql but I use the query grid at this point.


SELECT DISTINCTROW tblPtLocation.PtLocID, tblAreaList.AreaName,
tblBedsList.BedNumber, tblPatients.PtLName, [PtLocRmNum] & " " & [BedID_fk]
AS [RM Bd], tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm,
tblPtLocation.BedID_fk, tblPtLocation.PtLocRmNum
FROM tblPatients INNER JOIN (tblBedsList INNER JOIN (tblAreaList INNER JOIN
tblPtLocation ON tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON
tblBedsList.BedID = tblPtLocation.BedID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk
GROUP BY tblPtLocation.PtLocID, tblAreaList.AreaName, tblBedsList.BedNumber,
tblPatients.PtLName, [PtLocRmNum] & " " & [BedID_fk],
tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm,
tblPtLocation.BedID_fk, tblPtLocation.PtLocRmNum;


Thanks,
Linda

 

[Dale Fye]

LMB,

I would start out by creating a query (qry_Pt_Last_Location) to get the most
recent Patient Location Start Date and Time.

Select PtID, MAX(PtLocStDtTm) as Last_Date_Time
FROM tblPtLocation
GROUP BY PtID

Then join that query to tblPtLocation using an inner join on PtID and
PtLocStDtTm = Last_Date_Time. If you do this, you should only get a single
record from
tblPtLocation and by joining this to your other tables, should get only a
single record per patient. If you cannot figure this out, post back and
I'll take more time defining the second query.

HTH
Dale

 

[LMB]

I think I got it! I just added my tblPts to the query and the join was
already there. I looked at the sql and it showed an inner join but when I
clicked on the join line Inner join was not an option. I had to select a
Left Table and a Right Table then there are 3 options. Number one was
already selected. I tried the other 2 just for kicks and nothing changed.
I'm not quite clear on joins but I'll go read something on it and maybe it
will make sense. Thanks.

SELECT Max(tblPtLocation.PtLocStDtTm) AS Last_Date_Time, tblPatients.PtID
FROM tblPatients INNER JOIN tblPtLocation ON tblPatients.PtID =
tblPtLocation.PtID_fk
GROUP BY tblPatients.PtID;

LMB,

I would start out by creating a query (qry_Pt_Last_Location) to get the
most recent Patient Location Start Date and Time.

Select PtID, MAX(PtLocStDtTm) as Last_Date_Time
FROM tblPtLocation
GROUP BY PtID

Then join that query to tblPtLocation using an inner join on PtID and
PtLocStDtTm = Last_Date_Time. If you do this, you should only get a
single record from
tblPtLocation and by joining this to your other tables, should get only a
single record per patient. If you cannot figure this out, post back and
I'll take more time defining the second query.

HTH
Dale

Hi Everyone,

Access 2003. I am trying to get a query to show me only the records for
each patient that is the most recent based on the the
PtLocationStartDateAndTime field. Right now in my test database I have 2
patients who I show in 2 different rooms on different dates. I see all 4
records. I only want to see the record for the newest location. Here is
the sql but I use the query grid at this point.


SELECT DISTINCTROW tblPtLocation.PtLocID, tblAreaList.AreaName,
tblBedsList.BedNumber, tblPatients.PtLName, [PtLocRmNum] & " " &
[BedID_fk] AS [RM Bd], tblPtLocation.PtLocStDtTm,
tblPtLocation.PtLocEnDtTm, tblPtLocation.BedID_fk,
tblPtLocation.PtLocRmNum
FROM tblPatients INNER JOIN (tblBedsList INNER JOIN (tblAreaList INNER
JOIN tblPtLocation ON tblAreaList.AreaID = tblPtLocation.AreaID_fk) ON
tblBedsList.BedID = tblPtLocation.BedID_fk) ON tblPatients.PtID =
tblPtLocation.PtID_fk
GROUP BY tblPtLocation.PtLocID, tblAreaList.AreaName,
tblBedsList.BedNumber, tblPatients.PtLName, [PtLocRmNum] & " " &
[BedID_fk], tblPtLocation.PtLocStDtTm, tblPtLocation.PtLocEnDtTm,
tblPtLocation.BedID_fk, tblPtLocation.PtLocRmNum;


Thanks,
Linda