Records within 6 months of a date

[ant123]

Hi All,

I was hoping you could help me out with a problem. I have a database containing 2 years of healthcare data.

I have several tables:

• all_hospital_admissions
• first_hospital_admissions
• emergency_department_attendances

I want to find out how many emergency department attendances a patient has had 6 month previous to their first hospital admission and 6 month post their first hospital admission.

The date of their first hospital admission is stored in the 'first_hospital_admissions' table as the field name 'date_of_admission'
The date of emergency department attendances are stored in the 'emergency_department_attendances' table as 'date_of_attendance'
The patient ID is in all tables (patient_id)



How do I make a query to check this?

The other problem is that a patient may have attended the emergency department the day before or on the same day as the first hospital admission. Ideally I would like to remove these emergency department attendances from the count. Is this possible?

Any help would be greatly appreciated!

Thank you!

:-)

[June7]

One approach involves using DMin() domain aggregate function to pull the oldest date value from the admissions table. Something like:

SELECT * FROM emergency_department_attendances WHERE date_of_attendance > DateAdd("m", -6, DMin("date_of_admission", "first_hospital_admissions", "PatientID=" & [PatientID])) AND date_of_attendance < DateAdd("m", 6, DMin("date_of_admission", "first_hospital_admissions", "PatientID=" & [PatientID]));

[ant123]

Hi June7 - thank you very much for your reply. I have tried you formula but I can't get it to work.

Would you mind explaining exactly what each section means to I can work it out. I am trying to get a count of the emergency department attendances 6 months previous and 6 months post the first admission.

Thank you very much for your help - it is much appreciated

[ant123]

I think I need something like this to stat with:

I have joined the tables:

• first_hospital_admissions
• emergency_department_attendances

Then made a query and in the 'date of attendance' field (emergency_department_attendances) I have put:

>=DateAdd("m",-6,"[date_of_admission]")


this doesn't work but I think I need something similar...

[June7]

Yes, a query join could work if each patient has only 1 record in first_hospital_admissions.

You want all attendance records that fall between 6 months prior and 6 months after the admission date? criteria under [date of attendance] field:

> DateAdd("m",-6,"[date_of_admission]") AND < DateAdd("m",6,"[date_of_admission]")

[ant123]

Hi,

Thanks for your help. I have tried this formula but it keeps coming up with Data type mismatch in criteria expression

Any ideas why?

[ant123]

Im wondering if it because for some patients no record would be returned? (therefore something to do with null values?)

[June7]

Maybe. Try:

Not Is Null AND > DateAdd("m",-6,"[date_of_admission]") AND < DateAdd("m",6,"[date_of_admission]")

[ant123]

Thank you. I have solved it a different way but used some of your formulas! Thanks very much for your help :-)