Query to find students who are missing immunizations

[NoSweat88]

Hi. I am a complete Access newbie and I'm stuck, so I'm seeking out the help of you kind folks to solve a problem. This is my first post. I didn't see an introduction section so I apologize in advance if I breached forum protocol

I work for a school and I'm trying to design a query that will pull the student ID's of students who are missing immunizations. In my table, each record contains a student ID, the name of an immunization, the date of the immunization and other data. Some of the immunization names are long, but they usually contain an acronym that can be searched for. Each student can have multiple instances of an immunization, but on different dates. I'm trying to pull out students who don't have an immunization with the acronym tdap in the immunization name field.

I know this has to be pretty simple, but I'm not able to figure out how to set up the QBE to pull those student id's who are missing the tdap immunization.

All help is greatly appreciated.



NoSweat88

[ItsMe]

I would build a query using the Query Designer. Include your table in the designer window and drag the column that describes the immunization description to the grid (or dbl click the field name within the table). You can add the following to the criteria field within your grid, for the column you just added.

Not Like '*tdap*'

[NoSweat88]

I tried that, but it brings up ALL of the other immunizations, since none of them contain the acronym "tdap".

Just so I'm plain, here is a screen cap of the table. You'll note that there are multiple student id's (the first column) and multiple immunizations. I'm trying to find the student id that does not have a record with the tdap immunization acronym in any the records for a particular student id. I think I'm missing grouping the student id's first then look for the missing tdap, but I dont' know how to do it.



NS88

[HiTechCoach]

I will assume u have two tables:

1) Student
2) Student_immunization

Step 1) Make a query based on the table #2 Student_immunization that selects all the student IDs that DO have (Like *tdap*) the immunization.

Step 2) save the query . For example as: qryStudentsWithimmunizationTDAP

Now you have a query that lists all the students that DO have the TDAP immunization.

What you wnat is a list of all the students that are not on this list. In Relational Database logoc you want a list if the student IDs from the student table that do not match a student ID the list in the qyery qryStudentsWithimmunizationTDAP. Great news - There is a wizard for this! Access has a built in query wizard option called "Find Unmatched Query Wizard" that will walk you through the testes of created what you need.

These may help:

https://www.youtube.com/watch?v=lktivZpKutc

http://www.techonthenet.com/access/queries/unmatched.php and video

[ItsMe]

Thanks for bumping this thread, Boyd. I did not notice it earlier.

@NoSweat88
As mentioned you can manage the duplicates by using the Unmatched Query Wizard. If you do not have access to the Students table you can use the Totals tool within the Ribbon to add a Group By statement to a query that has 'Not Like'. However, for the best performance, I would use the Unmatched Query Wizard as described in the previous post.

If you are going to use the Totals tool, you will want to uncheck the 'Show' option within the grid for the field with Immunization Name. So, if you are NOT using the Unmatched Wizard, you would add the criteria <Not Like '*tdap*'> to the Immunization Name column and also uncheck the 'Show' option. Your query would only Show the student ID. The Totals tool is located under the Design tab of the Ribbon and is represented by a Sigma icon.

[NoSweat88]

Thanks to both HiTechCoach and ItsMe...I really appreciate the help. I'll give this a shot (no pun intended) and see what happens. The Student Management System we use does not do this particular type of report and the nurses in the district are pulling their hair out trying to submit reports to administration for state immunization compliance.

Again, huge thanks to both of you. I think the Unmatched Query will handle it the best. Let you know how it works out.

NoSweat88

[HiTechCoach]

If you do not have access to the Students table you can use the Totals tool within the Ribbon...

I do not see how your recommendation of a totaling with with Not Like *tdap" will never work.
How does a list of all the students that have had an type of immunization other than "tdap" tell you which noes are missing the tdap immunization. What if a student has NO immunization records? How will your totaling query include them?

What am I missing?

Without a master list of all the student ID I do not see any way to make this work.

[HiTechCoach]

Thanks to both HiTechCoach and ItsMe...I really appreciate the help. I'll give this a shot (no pun intended) and see what happens. The Student Management System we use does not do this particular type of report and the nurses in the district are pulling their hair out trying to submit reports to administration for state immunization compliance.

Again, huge thanks to both of you. I think the Unmatched Query will handle it the best. Let you know how it works out.

NoSweat88

You're welcome.

Keeps us updated on your progress.

[NoSweat88]

HiTechCoach and ItsMe,

Unmatched Query worked like a charm...got exactly what I wanted.

Thanks for the help. The way things are going, I'll be coming back here a LOT for advice.

NoSweat88

[HiTechCoach]

HiTechCoach and ItsMe,

Unmatched Query worked like a charm...got exactly what I wanted.

Thanks for the help. The way things are going, I'll be coming back here a LOT for advice.

NoSweat88

You're welcome. Glad we could assist.

Thanks for the update and sharing your solution.

[ItsMe]

Yeah, welcome to the forum.