Query problem

[Guest]

I'm trying to create a query to extract details of an admission for a hospital. The query is meant to extract all the details that belong to an indiviual admission number. This includes things like patient number and name etc (field values that only occur once in the admission - the patient only has one name). The only problem i'm having is that an admission can have more than one diagnosis code (unique to the diagnosed illness). A diagnosis occurs in order of rank (the primary ranked diagnosis results can result in having a secondary diagnosis e.g the flu, causing sickness). However, i only want to extract the primary diagnosis code in the query and ignore the secondary and tertiary etc etc. Can this be done??

 

[Sandra Daigle]

Sure but to help you I would really need to know more about your table
structure. At first glance it seems like you should have (at least) the
following tables:

Patient
-----------
PatientID*
LastName
FirstName
etc.

PatientAdmission
------------
PatientID*
AdmissionDate*
etc.

PatientAdmissionDiagnoses
-----------
PatientId*
AdmissionDate*
Rank*
DiagnosisCode

* primary key fields

In the last table, the 'rank' field would be a sequential number that tells
you whether the diagnosis code is the first, second, third, etc.

Then your query would be based on a join of all three tables with criteria
on the Rank field of the third table selecting only the rows where Rank=1.