Return Records That Don't Meet Criteria

[Guest]

I am trying to create a query that returns students that have not attended
mandatory classes. I have a table with classes and a checkbox for the ones
that are mandatory. I have a students table. Then I have a training table to
record what classes the students took. So how would I go about listing the
students who have not attended the mandatory classes? Thanks

 

[Casey via AccessMonster.com]

Richard,

I am assigning erroneous names to your tables since those were not given as
well as their attributes:
"table with classes and a checkbox for the ones that are mandatory" = Classes
(class, mandatory)
"students" = Student (studentid, fname, lname...)
"training table" = Training (trainingid, studentid, class)

here is a SQL statement that I threw together, let me know if it works...

SELECT s.*
FROM Student s
WHERE NOT EXISTS
(SELECT *
FROM Classes c
WHERE c.mandatory = Yes
AND NOT EXISTS
(SELECT *
FROM Training t
WHERE t.class = c.class
AND t.studentid = s.studentid));

That should hopefully work for you. If it doesn't then in the last select
statment you might need to change "t.class = c.class" to "t.class != c.class",
negative logic confuses me...Goodluck!!

HTH

 

[Guest]

Thanks Casey, but it doesn't work. I get 0 records and I know I ahould have
4 students that didn't take the mandatory classes.

 

[Casey via AccessMonster.com]

Just bumping this back up also, I will create some tables and figure out why
this is not working today and get back to you Richard

Thanks Casey, but it doesn't work. I get 0 records and I know I ahould have
4 students that didn't take the mandatory classes.

Richard,

[quoted text clipped - 30 lines]

 

[Casey via AccessMonster.com]

Richard,

This query will give you the ones that DO meet the criteria, for some reason
I cannot get it to return the ones that DONT meet the criteria. I will look
at it again later but right now I cannot spend any more time on it. Sorry and
I hope this helps you out!

SELECT s.*
FROM Student s
WHERE NOT EXISTS
(SELECT *
FROM Class c, training t
WHERE c.mandatory = Yes
AND NOT EXISTS
(SELECT *
FROM Training t
WHERE t.cid = c.cid
AND t.sid = s.sid));

Thanks Casey, but it doesn't work. I get 0 records and I know I ahould have
4 students that didn't take the mandatory classes.

Richard,

[quoted text clipped - 30 lines]