Query where No match in 2 join tables

[TomHefferin]

I am currently building a Training database to assign Trainees to particular Courses. One particular query is giving me issues.

It includes 2 join tables

• tblCourseMats: Used to assign required course materials to a particular Course
• tblTraineeMats: Used to track which course materials a particular trainee already has.

I need to create a record set of Trainee details for those Trainees who do not have the required Mats for upcoming courses and which Mats they are missing.

The Record Set should:

1. Not include Trainees who already have the required Mats (TraineeMats <> CourseMats)
2. Include Trainees who don't have any Mats(not listed in tblTraineeMats)
3. Only list a particular Trainee once (tblTraineeMats can contain several 'other' Mats)
4. Include Date of Course (filtered to next 3 weeks)
5. Include which Course Mats are missing

The attachment shows an image of the query design I have so far. It fulfils 1-4, but my attempts to get it to show the correct 'missing Mats has failed. The grouping chooses the First Mats the Trainee has, rather than the Mats the course requires.

I would be interested to hear from anyone who can either explain how I can fix this, or offer alternatives.

Thanks in advance.

 

[Ranman256]

to get missing items, do an OUTER join on person/course
all persons, some courses
use just the minimal tables (2?) and call the query qsMissingCourses.
THEN join qsMissingCourses to your masterpiece shown in the image.

 

[The_Doc_Man]

Concur with Ranman's approach.

However, this might be a bit trickier than it looks. (OK, maybe it ALREADY looks pretty tricky?)

You will need a layered set of joined queries to make this work correctly. I might try first joining your tblCourseMats and tblPersMats by making a new query and adding only those two tables. In design-grid mode, create a relationship between field MaterialsID in both tables, but with the JOIN option of "all entries in tblCourseMats" and "only matching entries from tblPersMats." Include the PersonnelID field from tblPersonnelMats. NOW add a calculated field to the query, call it "PersHasMats" and make it an "Not IsNull(tblPersMats.PersonnelID)". From this point on, you can include that field in whatever else you join and it will be a True/False flag to show that the person does (true) or does not (false) have the materials.

I see that you have an "IsNull" in the criteria line under the Materials ID from the personnel materials table, but I think that it doesn't work right in that context. You do better by making the IsNull a field to be returned and then let the next layer of criteria do a filter based on the state of this flag.


NOW you can link this new query to your other tables, joining to the

 

[TomHefferin]

Ok, thanks to both of you - that's given me something to work with. I'll get back to you after I've played around with it.

 

[TomHefferin]

Ok, I've tried creating the Outer Join as suggested (LeftOuterJoin.png) but it gives some weird results (LeftOuterJoinResults.png).

So I'm guessing there is something wrong with the table linking on the Back-end.

I've attached an image of the tables involved here. (CourseMats.png)

Should tblMaterials only be joined to either tblCourseMats or tblPersMats?

I was never really comfortable with that loop as I can't work out the consequences.

Thanks