Query Help

[jbailey4545]

I have a linked table using ODBC. This is a read only table. I am trying to pull the latest record for patients. Some patients have multiple records and the only way to distinguish them is using the program_seq column. This is a number field starting at 1 and there is no limit to how high it can get. Each record represents when a patient was admitted and discharged. For example I am looking at a patient that has been admitted 3 times and discharged 2 times. The patient is still on the program the program_seq is 3. The primary key is a unique patient number. Any help would greatly be appreciated.



Eaxmple

I will also need to pull the newest records even if they have been discharged.

[pbaldy]

Does this help?

http://www.baldyweb.com/LastValue.htm

[orange]

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

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.

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));