John,
I was able to get your query to work but am having a problem with the
results involving patients who have been readmitted more than once. Here is
the output of the query for a patient who has been readmitted three times.
MedRecordNumber Pt Ctr Num AdmitDate DischargeDate AdmitDate2
115250 521926 1/15/08 1/17/08
11/18/08
115250 653213 3/5/08 3/10/08
11/18/08
115250 734234 3/18/08 3/22/08
11/18/08
115250 923454 11/18/08 11/25/08
When a patient has only been readmitted once in the year, the query works
correctly but for patients readmitted several times, my query is using the
very last admit date for AdmitDate2. I tried taking out the Max function but
got an error. Any way to get the admitDate2 values to appear sequentially?
Here is my sql:
SELECT PatientTable2.[Physician ID], PatientTable2.[Medical record number],
PatientTable2.[Patient control number], PatientTable2.[Admit date],
PatientTable2.[Discharge date], (SELECT Max([Admit date]) as Prior FROM
PatientTable2 as Temp WHERE Temp.[Medical record number] =
PatientTable2.[Medical record number]
AND Temp.[Admit date] >= PatientTable2.[Discharge date]) AS AdmitDate2
FROM PatientTable2;
John Spencer said:
You seem to be missing the a reference in the where clause to
=PatientTable.[Patient control number]
(SELECT Max([Discharge date]) AS Prior
from PatientTable as Temp
where
temp.[Patient control number]=PatientTable.[Patient control number]
and temp.[Admit date] >=
PatientTable.[Discharge date] as PriorDate)
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Chuck said:
John,
Thanks for your help. I keep getting a syntax error and it is highlighting
the following code:
(SELECT Max([Discharge date]) AS Prior from PatientTable as Temp where
temp.[Patient control number] and temp.[Admit date] >=
PatientTable.[Discharge date] as PriorDate)
I have tried substituting PriorDate from the first prior but this didn't
solve it. Patient Control Number is AccountNumber, Discharge Date is
DischargeDate and Admit Date is AdmitDate. I thought it would be easier to
rename them in my example. Any idea why there is a syntax error? I wrote
out the rest of the query but the syntax error only highlights this section.
Thanks,
:
One possibility:
SELECT PatientNumber, EncounterNumber, AdmitDate, DischargeDate
, (SELECT Max(DischargeDate) as Prior FROM PatientTable as Temp WHERE
Temp.PatientNumber = PatientTable.PatientNumber
AND Temp.AdmitDate >= PatientTable.DischargeDate) as PriorDate
FROM PatientTable
WHERE DateDiff("D",Nz((SELECT Max(DischargeDate) as Prior FROM PatientTable as
Temp WHERE Temp.PatientNumber = PatientTable.PatientNumber
AND Temp.AdmitDate >= PatientTable.DischargeDate),#1/1/1900#),AdmitDate) < 30
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Chuck W wrote:
Hi,
I have a table that contains PatientNumber, EncounterNumber, AdmitDate and
DischargeDate. a Patient (and PatientNumber) may have several visits or
encounters and therefore several EncounterNumbers. I am trying to find all
records in which the admit date for a return visit by a patient is within 30
days of the previous discharge date. Here is my data:
PatientNumber EncounterNumber AdmitDate DischargeDate
100100 3040560 06/01/2009 06/03/2009
100100 4212045 06/26/2009 06/30/2009
In this instance, the admit date of the second encounter chronologically was
within 30 days of the discharge date of the previous encounter. Can someone
help with how to identify these records?
Thanks,
