Query with Date Range and Duplicates (1 Viewer)

Hello,

I'm looking for the best way to create a query using two tables. One table has patient ID, diagnosis date, and diagnosis. The second table table has patient ID, lab value, admission date and discharge date. I would like to create a new table that shows the patient ID, lab value and diagnosis from the first table but only if the diagnosis date is between the admission and discharge date from the second table. Some patients have multiple admissions so there are duplicates in the patient ID field. What is the best way to query this information?

Hi. This very doable but should be no need to create a table out of the query. You should be able to use the query wherever you intend to to use the new table.

Bring both tables into the query designer. Bring down all the fields you want to show. JOIN your tables like so:

PatientID to PatientID
AdmissionDate to DiagnosisDate
DischargeDate to DiagnosisDate

Next, you will need to go into SQL view and manually edit the SQL. There will be a section that says "INNER JOIN" make these changes:

AdmissionDate = DiagnosisDate
to AdmissionDate<=DiagnosisDate

DischargeDate = DiagnosisDate
to DischargeDate >= DiagnosisDate

Save it and theres your query.

Using the QBE, create a query that joins the two tables on PatientID. Select the columns you want from each table. Then switch to SQL view to add the WHERE clause.

Where tblPatient.DiagDT Between tblLabTests.AdmitDT and tblLabTests.DiscDT

I took some liberties with your column names. Please use your own.

Plog beat me. I was going to suggest a non-equi join but went with the WHERE clause instead. Try both to see if one is faster than the other.