This uses 2 queries - that select Patient info for patients still on the program
LatestPatientrecordForPatientStillOnProgram and Patient's who have been discharged.
The query LatestPatientrecordForPatientStillOnProgram has the following sql
Code:
SELECT tblPatient.Client_id, tblPatient.Program_seq, tblPatient.Admit_date, tblPatient.Discharge_Date
FROM tblPatient
WHERE (((tblPatient.Discharge_Date) Is Null));
This query should give you the results required.
Code:
SELECT tblPatient.Client_id, tblPatient.Program_seq, tblPatient.Admit_date, tblPatient.Discharge_Date
FROM tblPatient LEFT JOIN LatestPatientrecordForPatientStillOnProgram ON
tblPatient.Client_id = LatestPatientrecordForPatientStillOnProgram.Client_id
WHERE (((tblPatient.Discharge_Date)=
(select max(discharge_date) from tblPatient as Z where z.client_id =tblPatient.client_id)) AND
((LatestPatientrecordForPatientStillOnProgram.Client_id) Is Null))
UNION
SELECT tblPatient.Client_id, tblPatient.Program_seq, tblPatient.Admit_date, tblPatient.Discharge_Date
FROM tblPatient
WHERE (((tblPatient.Discharge_Date) Is Null));