MS-Access - Determining if the next record meets a criteria

I have attached a list of sample data I have to evaluate for repeat admissions within 30 days of the last admission date.  The data is organized by Unit Number and Admission Dates in a descending order.  If the subsequent admission date for the same Unit Number is within 30 days of the current  record's admission date, then it should be marked as "Y" for readmission.

Can this be reflected in a query?

Thanks

Glen
lACEdATA.xlsx
GPSPOWAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
You use UnitNumber, not AccountNumber to identify readmission?

You can do something like:

SELECT T.UnitNumber, T.AccountNumber, T.AdmitDateTime
, Max(T2.AdmitDateTime) as PrevAdmitDate
FROM yourTable as T
LEFT JOIN yourTable as T2
ON T.UnitNumber = T2.UnitNumber
AND T.AdmitDateTime > T2.AdmitDateTime
GROUP by T.UnitNumber, T.AccountNumber, T.AdmitDateTime

This would give you each admittance and the previous admittance for that particular UnitNumber.  You could then wrap that up as a subquery to get the indication of whether it is a readmittance.

Select subQ.UnitNumber, SubQ.AccountNumber
subQ.AdmitDateTime, subQ.PrevAdmitDate
, iif(IsNull([PrevAdmitDate]), "No", iif(DateDiff("d", subQ.PrevAdmitDate, subQ.AdmitDateTime) <= 30, "Yes", "No")) as Readmit
FROM (
SELECT T.UnitNumber, T.AccountNumber, T.AdmitDateTime
, Max(T2.AdmitDateTime) as PrevAdmitDate
FROM yourTable as T
LEFT JOIN yourTable as T2
ON T.UnitNumber = T2.UnitNumber
AND T.AdmitDateTime > T2.AdmitDateTime
GROUP by T.UnitNumber, T.AccountNumber, T.AdmitDateTime
) as SubQ
0
 
GPSPOWAuthor Commented:
I had to reverse the two dates in the datediff function and it worked perfectly.

The UnitNumber is the patient Medical Record number which stays with the patient no matter how many times he or she is admitted.  The account number changes with every admit.  So I am looking when the UnitNumber matches for subsequent admissions.

Thanks

Glen
0
 
Dale FyeCommented:
glad to be of assistance.
0
 
Dale FyeCommented:
actually, you might want to use:

IIF(DateDiff("d", NZ([PrevAdmitDate], [AdmitDate] - 31), [AdmitDateTime]) <=30, "Yes", "No") as Readmit

This should work better as it handles the NULL [PrevAdmitDate] inside an NZ function instead of another IIF( ) statement.
0
 
GPSPOWAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.