Join Problem

[Guest]

What I'm trying to do is get a list of people that have not passed four parts
of something. Currently I have the following relationships in my database

Candidate Table
CID - Primary Key

Enrollment Table
CID + ClassID - Primary Key

Class Table
ClassID - Primary Key
Contains subject from subject table

Subject Table
Subject - Primary Key (Only four subjects)

The query I have is listed below:

SELECT DISTINCT c.CID, c.firstname, c.lastname, c.street, c.city, c.ZIP
FROM subject AS s INNER JOIN (Class INNER JOIN (candidates AS c INNER JOIN
enrollment AS e ON c.CID = e.CID) ON Class.ClassID = e.ClassID) ON s.Subject
= Class.Subject
WHERE (((s.Subject)='Class1' Or (s.Subject)='Class2' Or (s.Subject)='Class3'
Or (s.Subject)='Class4') AND ((e.Score)<50 Or (e.Score) Is Null))
OR (c.passedexam) is null;

This query does work, however, there's one problem. Prior to me taking over
this database, the users were entering a date in the passedexam field of the
candidates table when someone had completed all four parts of the exam. I no
longer enter any values in this field, as i created the enrollment table that
holds scores as well as the date they took a part of the exam. The problem
is that access created an inner join on the candidates and enrollment tables.
So, if someone is not in the enrollment table the query does not return them
even if passedexam is NULL. I'm not real good on joins and I would
appreciate if someone could assist me. Let me know if I can provide further
details.

 

[John Viescas]

SELECT c.CID, c.firstname, c.lastname, c.street, c.city, c.ZIP
FROM Candidates As c
WHERE c.CID NOT IN
(SELECT e.CID
FROM subject AS s
INNER JOIN (Class
INNER JOIN enrollment AS e
ON Class.ClassID = e.ClassID)
ON s.Subject = Class.Subject
WHERE ((s.Subject='Class1') Or (s.Subject='Class2') Or (s.Subject='Class3')
Or (s.Subject='Class4')) AND (e.Score >50)
GROUP BY e.CID
HAVING Count(*) = 4)

You can remove the NOT from WHERE c.CID NOT IN to find out all candidates
who have passed all four classes with a score greater than 50.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)