prevent duplication

[Guest]

I'm downloading data from a ODBC source over which I have no influence. The
data is patient admission, discharges, unit assignments (and a few other
fields.)
In order to the the last admission, last discharge, and last unit assignment
I created queries that give me patient IDnumber and max dates for all three
tables. The patient active or inactive status is determined by the Discharge
field Is Null (which makes the patient active).All works well, except when I
pull all the data together (with joint that shows all admissions), I get the
following:
Pat A Adm. 1/1/05 Disch. 2/15/05 Inactive
Pat A Adm. 3/1/05 Disch. 4/1/05 Inactive
Pat A Adm. 5/1/05 Active
Pat B Adm. 1/1/05 Disch. 1/15/05 Inactive
Pat B Adm. 2/1/05 Disch. 3/1/05 Inactive
I need to get for Pat A the Active data only, that is Admission 5/1/05, for
Pat B I need the latest Admission 2/1/05 (because we may need to enter data
for discharged patients, and also need this for reports. Thus each patient
needs to be represtend only once with the latest admission date. I tried to
run the Max of Admission date on the query the pulls the above, but that
doesn't work. Help, please.
As always, thanks for your assistance.
Brigitte P

 

[ChrisR]

Brigitte,

If the relationship of Patient + Admin date is unique (Patient A does not
have 2 records with same Admin Date) then the way I would do this is in two
steps.

1. Create a select query of Patient/Admin Date group by Patient, Max of
Admin Date.
2. Create query using step1 select query joining back to original table
with join on Patient and Admin date. This way you are saying I want to only
get back the records for each patients unique max admin date.

This will give you 1 record per Patient for their max admin date. I think
that this is what you were looking for. In this step2 you can obviously
pull in the discharge and active/inactive status from the original table and
it will be the data that is relevant to the Patient/Max Admin date.

Hope this helps.

c-