Many to Many Query

[Sandpiper]

Hi
I'm recording information about people attending training courses. There are three tables
1 - tblStaff (StaffID, FirstName, Surname)
2 - tblCourse (CourseName)
3 - tblAttendance(StaffID,CourseName,Date)
Table three is a many to many junction table as you can see.
(This is a simplified version of my tables).
I need to query this table to find all staff who haven't attended a certain course.

If I do a standard query where my criteria says <>CourseNameA, that doesn't stop the staffID of people who have been on courseA appearing on my list, as they have been on coursesB and therefore still match the criteria of <>CourseNameA. - I hope that makes sense.

What I need to do is get that list, but remove anyone who has been on CourseA.

Can anyone help me acheie this please.

Thanks very much

 

[neileg]

You have to think out of the box on this one. You need to be able to produce a dataset that contains every combination of staff and course and then match this to your table of attendances. Where this returns a null from tblAttendance, you know they haven't been!

The way to produce this dataset is to create a Cartesian Product, which is something we usually try very hard to avoid. It's very easy, though! Create a new query and add in tblStaff and tblCourse. Do not create a join between these tables! If Access puts a join in, take it out. If you run this query you will find you have a record for every staff and every course. Now create a new query. Add in the query just created and tblAttendance. Join with a left join both StaffID and CourseName. Now apply a criterion IsNull to tblAttendance.

Et voila!

 

[Sandpiper]

Thanks for the response - sorry took so long to reply, I was on holiday.

Anyway. I'm still slightly confused.

I've created the query which gives a list of absolutely everyone with every course.

Then i've created a query which links that to the attendance table. You mentioned that I should do left joins on both the staff ID and course. Do you mean from with the combined table on the left hand side?

Then you said look for Nulls on the attendance table, but under which field? Also, I haven't specified which course I don't want to see - where do I do that?

sorry if these are silly questions, I know what's supposed to happen, but am stuck in teh box at the moment.

Think i'm panicking, so can't sort it out.

If you could help, i'd be very grateful.

 

[CraigDolphin]

Hi,

have a look at the attached db and see if it helps you figure this out. All it does is compile two lists.

The first, a parameter query, simply lists staff members that attended that particular class.

The second list (query), simply lists staff members who do not appear in the first list. This uses a left join between the staff table and the first query, and restricts the output to staff members with an ID that does not appear (is null) in the first query.

Hope this helps.

 

[Corinnex99]

Hi
I'm recording information about people attending training courses. There are three tables
1 - tblStaff (StaffID, FirstName, Surname)
2 - tblCourse (CourseName)
3 - tblAttendance(StaffID,CourseName,Date)
Table three is a many to many junction table as you can see.
(This is a simplified version of my tables).
I need to query this table to find all staff who haven't attended a certain course.

If I do a standard query where my criteria says <>CourseNameA, that doesn't stop the staffID of people who have been on courseA appearing on my list, as they have been on coursesB and therefore still match the criteria of <>CourseNameA. - I hope that makes sense.

What I need to do is get that list, but remove anyone who has been on CourseA.

Can anyone help me acheie this please.

Thanks very much

I'm looking for EXACTLY the same thing! Does anyone have any finished examples that I can view?

Thanks!!!

 

[CraigDolphin]

Is this a class project?