Previous Record

[Ray1127]

I have a query that returns 4 fields. PatientID, Service_Start_Date,Service_End_Date,Service_Type.

Service_Type can be 1 of 3 values (Inpatient, ER, Urgent care.

Heres a sample

Patient ID, Admit, Disch, Type, ER/Admit
1 3/10/2011 3/10/2011, ER False
1 3/10/2011 3/14/2011, Inpatient True
2 5/14/2011 5/14/2011 ER False
2 5/17/2011 5/19/2011 Inpatient False
2 5/28/2011 5/28/2011 UC False
2 5/29/2011 6/3/2011 Inpatient True


I need to be able to check if the Service_Start_Date of an Inpatient Record is within 1 day of the service end date of the previous UC/Er record for the same Patient Id then the Inpatient Admit Record should show true as in the sample above. Is it possible to refer to the previous record in a query or does this have to be done through VBA which slows it down considerably.

 

[PHV]

A starting point:

SELECT A.PatientID, A.Admit, A.Disch, A.Type
, IIf(A.Type='Inpatient' AND (A.Admit-(SELECT Max(Disch) FROM daTable WHERE PatientID=A.PatientID AND Disch<=A.Admit))<=1,'True','False') AS [ER/Admit]
FROM daTable AS A

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jedraw]

Allen Browne has examples of the use of subqueries that may be useful to you also. One of the examples is a query in which he uses the value(s) from another record in the table.

These can be found at

http://allenbrowne.com/subquery-01.html#AnotherRecord