Tables one with specific Dates the Other With a Range

[AccessMih]

Hello All,



I was recently working on an issue in Excel and discovered that I needed to use Access. Being a complete novice with Access I'm turning to you all for help.

What I have is three tables:
1. Patient
2. Part A - Acute Care
3. Part D - Prescription

The Part D - Prescription includes a date where the prescription is filled by a pharmacy.
The Part A - Acute Care has a date range for when the patient was in a hospital. (Note - there are multiple date ranges for some patients)

I would like to know if the prescription was filled when the patient was in the hospital. Can you all help me write the query?

[CJ_London]

Strongly recommend you do the following:

1. remove spaces and non alphanumeric characters from table and field names - they can cause misleading errors
2. rename your ID fields to something more relevant such as patientID (I prefer patientPK to identify it as the primary key)
3. join your tables on the PK field - so your part tables need a patientFK (foreign key field)

but to answer your question you need a left join between partD and partA tables (return all partD and only partA where there is a match)

then in the criteria

PartD.dateOfFill Between PartA.BeginStay and PartA.EndStay OR PartA.ID is Null

[AccessMih]

Thank you I was able to follow your directions and get the result I was looking for.