Multi-table query question

[mstng07]

I'm not quite sure on how to go about this query. What I need to know is if a certain patient has had an immunization or not. I need one query to show the patients who have had an immunization and another to show the ones who have not had the immunization.

I have two tables.

Appointments

Date_of_Service​

Patient_Name​

Chart_ID​

Immunizations

Patient_Name​

Immunization_Date​

Chart_ID​

They are joined on Chart_ID.

Any help would be greatly appreciated!

 

[spcepickle]

If the Immunization_Date is left blank until the patient gets their immunization you can make a query searching Is Null and one that searches Is Not Null.

 

[mstng07]

well the information in the appointments table is from the scheduling system and the immunization information is from the electronic health records system so there could be multiple appointments for one patient. if that makes any sense.

thank you for your help though! If there wasn't 13,000 appointments i'd have to go in and edit to put in that information, that would work wonderful Thanks again though!

 

[mstng07]

Okay i don't know if this is possible or not. and if it is how it can be done

i need to create a query that basically combines the two tables. if they have an appointment but no immunization, it still needs to show in the report (query). is this possible?

thanks in advance!

 

[georgedwilkinson]

Yes, if the data is there, it is possible. Show us what you've got so far.

 

[mstng07]

SELECT Appointments.[Date of Service], 
Appointments.Patient, 
Appointments.ID, 
Appointments.Physician, 
Appointments.Phone, 
Appointments.Chart_Id, 
Immunizations.Patient, 
Immunizations.Sex,
Immunizations.DOB, 
Immunizations.Chart_Id, 
Immunizations.Preferred_Provider, 
Immunizations.Enc_Location, 
Immunizations.Phone, 
Immunizations.Zip, 
Immunizations.Imm_Date, 
Immunizations.Imm_Title, 
Immunizations.CPT_Code
FROM Immunizations, Appointments
WHERE Appointments.Chart_Id=Immunizations.Chart_Id;

that shows for the ones who had an appt and an immunization date. i think, i i'm correct, i need a statement like where appointments.Chart_Id does not equal immunizations.chart_Id? i'm not sure

 

[mstng07]

well i think i got it. i went into the design view and changed the criteria to is null or is not null and it seemed to work. just have to wait and see if this is what they want for sure. thanks everyone for all of your help

 

[georgedwilkinson]

Does this query work for what you want? If that is true, then you only need another query for appointments without an immunization record, is that true?

If yes:
Appointments.Patient,
Appointments.ID,
Appointments.Physician,
Appointments.Phone,
Appointments.Chart_Id,
FROM Appointments
WHERE Appointments.Chart_Id not in
(Select Immunizations.Chart_Id from Immunizations);

Should give you the people without immunizations, if I'm understanding you correctly.

If not, start over with a fresh explaination of your problem, 'cause I don't get it.