Query Help

[PRodgers4284]

I need to join two dates from two tables, "Patient" and "Admission". I have Date from the "patient" table and a date from the "Admission" table and i want to calculate the number of days between the two dates, i think i need an inner join to join the two tables but im unsure how to do this. Can anyone point me in the right direction?

 

[DCrake]

You have several issues here

1st the relationship between the the two tables is the patient. This is what you need to join on.

2nd does the patient actually got an admission date? You will need a left join to ensure that all patients are listed even if they do not have an admission

3rd if both dates are present are they in cronological order?

caclulation = DateDiff("d",PatientDate,AddmissionDate) This will give you the number of days between the two if both dates are present.

You may need to change this to

DateDiff("d",PatientDate,Nz(AddmissionDate,PatientDate)) so that if no admission date exists then it will return zero not error becouse of the null value in the admission date.

David

 

[PRodgers4284]

You have several issues here

1st the relationship between the the two tables is the patient. This is what you need to join on.

2nd does the patient actually got an admission date? You will need a left join to ensure that all patients are listed even if they do not have an admission

3rd if both dates are present are they in cronological order?

caclulation = DateDiff("d",PatientDate,AddmissionDate) This will give you the number of days between the two if both dates are present.

You may need to change this to

DateDiff("d",PatientDate,Nz(AddmissionDate,PatientDate)) so that if no admission date exists then it will return zero not error becouse of the null value in the admission date.

David

David thanks for the reply, the patient id is the relationship in each table. The patient has a date for "positive toxin" in the patient table, the admission table has an admission date for the patient, i need to get the no of days between the "positive toxin" and the "admission date". The dates will be in crono order, have you got anything i could use to do the table join, ive never done one before, would appreciate some help on how to start

 

[DCrake]

If you bring both tables into your query and do a join on the patient id this should give you the relationship. However is each patient only ever going to have one admission date? I fear not. So some how you need to relate the positive toxin date to the admission date you may need to look at episode numbers

 

[PRodgers4284]

If you bring both tables into your query and do a join on the patient id this should give you the relationship. However is each patient only ever going to have one admission date? I fear not. So some how you need to relate the positive toxin date to the admission date you may need to look at episode numbers

David you have uncovered a mistake in my patient table, a patient can have more than one toxin date as this will be different for each admission, thankyou for that . I have changed the table structure so that the dates are in the same table, no need for a table join now. Im assuming the calculation between the dates should be fairly straight forward now, i want to save the calculations so il need an append query to do this?