Adding a field to a subquery

[Chuck W]

Hi,
I have a query with a subquery build into it below. This returns fields
such as [Medical record number] which is a patient ID, [Patient control
number] which is a visit id, Admit Date and Discharge Date. It also creates
a field called AdmitDate2 which is the next admit date for a patient who has
been readmitted. It is null if there is no readmission. What I want to do is
to add a field called ReadmitPCN which is the Patient control Number for the
readmission. I know if is already there in the next record but I want to
place it in the same record that has the AdmitDate2 field. I cannot seem to
add this field correctly. Can someone help
--------------------------------------------------------------------------------------
SELECT PT2_1.[Medical record number], PT2_1.[Patient control number],
PT2_1.[Admit date], PT2_1.[Discharge date], (SELECT MIN([Admit date])
FROM PatientTable2 AS PT2_2
WHERE PT2_2.[Medical record number]
= PT2_1.[Medical record number]
AND PT2_2.[Admit date] > PT2_1.[Admit date]) AS AdmitDate2 INTO
tbl30DayReadmit
FROM PatientTable2 AS PT2_1
ORDER BY PT2_1.[Medical record number], PT2_1.[Admit date];

 

[Daryl S]

Chuck -

To get two fields from one record you must join the two tables (in this case
two of the same table) together. You still need the criteria for linking
them, so it involves a third copy of the table. Try this (untested):

SELECT PT2_1.[Medical record number], PT2_1.[Patient control number],
PT2_1.[Admit date], PT2_1.[Discharge date], PT2_2.[Admit date] AS AdmitDate2,
PT2_2.[Patient control number] AS ReadmitPCN
INTO tbl30DayReadmit
FROM PatientTable2 AS PT2_1, PatientTable2 AS PT2_2
WHERE PT2_2.[Medical record number] = PT2_1.[Medical record number]
AND PT2_2.[Admit date] = (SELECT MIN(PT2_3.[Admit date])
FROM PatientTable2 AS PT2_3
WHERE PT2_3.[Medical record number] = PT2_1.[Medical record number]
AND PT2_3.[Admit date] > PT2_1.[Admit date])
ORDER BY PT2_1.[Medical record number], PT2_1.[Admit date];