An Unmatched Query?

[Jon M.]

Okay I've tried every way I can think of but I cannot for the life of me get
this to work. I have 3 tables, Students, Classes, StudentsAndClasses. And
yes this db is loosely based on the template from Microsoft(Classes).
StudentsAndClasses links my Students table to the Classes table, so if I want
to enroll a student in a class it creates a new record in the
StudentsAndClasses table called StudentClassID(an autonumber),
StudentsAndClasses holds both the class and student info. Each student has
only one record in Students, likewise with Classes each class only holds one
record, but a student or class may appear multiple times in the
StudentsAndClasses table based on enrollment. One of the fields in
StudentsAndClasses is a Date_Completed field, which a date is entered into
once the class is finished.

Here is what I am trying to do, I am trying to run a query so I may then run
a report which shows me all of my classes, and the students who have 1. not
been enrolled in the class and 2. if they were enrolled they didn't complete
it, so the Date_Completed field is blank or null.

I think I have to create a query based on Students and StudentsAndClasses,
then create another query using that one with Classes, I am not sure if an
unmatched query is what I want to use or if I am grouping my tables correctly
in the subquery, or what I'm doing to be honest.

I have tried this over and over and I can't seem to get it right. My common
fields are "StudentID" links Students to StudentsAndClasses, and "ClassID"
Links StudentsAndClasses to Classes. I want my query to group by class and
show every student who hasn't been enrolled or completed the class. I
imagine on my report I'll have to hide the duplicate class names but that's
not a problem. getting the query to work right is my problem. Am I making
sense? As always any help is appreciated.

 

[Lord Kelvan]

try

SELECT *
FROM classes
RIGHT JOIN (students
LEFT JOIN StudentsAndClasses
ON students.studentid = StudentsAndClasses.Studentid)
ON classes.classid = StudentsAndClasses.classid
WHERE (((StudentsAndClasses.Studentid) Is Null))
OR (((StudentsAndClasses.date_completed) Is Null));

Hope this helps

Regards
Kelvan

 

[Jon M.]

It was the "or" that I was missing. You are a kind gentleman. Thank you.