C
Chuck W
I have a table Called PatientTable2 that has PhysicianID, Medical Record
Number which is like a Patient ID number, Patient Control Number which is
issued for every admittance (If a patient comes in more than once their
Medical Record Number stays the same but get a new Patient Control Number
each time), Admit Date and Discharge Date.
Looking at Allen Browne’s page, I have written the following query that has
a subquery but it is not giving me what I want. I want to create another
field called AdmitDate2 that will list a date value for all people that have
been readmitted. The value will be the admit date for the next visit. So if
a patient has made three visits, the first two records would have an
AdmitDate2 value of the next records Admit Date. However, my query if
returning the following results below. Can someone help me with the query so
AdmitDate2 just returns the next Admit Date rather than the last?
PhysicianID MRN PCN AdmitDate DischDate AdmitDate2
14060 145152 18292676 1/15/2008 1/17/2008 11/18/2008
14060 145152 16451785 3/5/2008 3/10/2008 11/18/2008
14060 145152 14365814 3/18/2008 3/22/2008 11/18/2008
14060 145152 11074851 11/18/2008 11/25/2008
---------------------------------------------------------------------------------------------
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
ORDER BY PatientTable2.[Medical record number], PatientTable2.[Admit date];
Number which is like a Patient ID number, Patient Control Number which is
issued for every admittance (If a patient comes in more than once their
Medical Record Number stays the same but get a new Patient Control Number
each time), Admit Date and Discharge Date.
Looking at Allen Browne’s page, I have written the following query that has
a subquery but it is not giving me what I want. I want to create another
field called AdmitDate2 that will list a date value for all people that have
been readmitted. The value will be the admit date for the next visit. So if
a patient has made three visits, the first two records would have an
AdmitDate2 value of the next records Admit Date. However, my query if
returning the following results below. Can someone help me with the query so
AdmitDate2 just returns the next Admit Date rather than the last?
PhysicianID MRN PCN AdmitDate DischDate AdmitDate2
14060 145152 18292676 1/15/2008 1/17/2008 11/18/2008
14060 145152 16451785 3/5/2008 3/10/2008 11/18/2008
14060 145152 14365814 3/18/2008 3/22/2008 11/18/2008
14060 145152 11074851 11/18/2008 11/25/2008
---------------------------------------------------------------------------------------------
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
ORDER BY PatientTable2.[Medical record number], PatientTable2.[Admit date];