find non-matching records, sort of

[Guest]

My apologies if a variation of this question appears elsewhere; I've never
posted here before and can't tell if I just can't find my original question
or I somehow didn't get it posted.

I have two tables in a one-to-many relationship: "students" (one) and
"courses" (many). They are linked via a "studentID" field. Every record in
"students" has multipe matching records in "courses." What I need is a query
that will tell me which students have not taken a particular course. That
is, the record in the parent table does not have a record matching a
particular criteria in the child database, even though there are multiple
other records in that child database for that parent. Clear as mud?

The "Find Non-Matching Records" query wizard comes up blank, of course, but
there are matching records for the criteria it will let me specify...

Thanks.

 

[Jeff Boyce]

I may be reading too much into your description ...

A common design for students and courses is three tables. One table for
students (any/all students), one table for courses (any/all courses), and
one table to resolve the many-to-many relationship. If your situation is
that a course may be taken by zero to many students, and a student may take
zero to many courses, you have a many-to-many relationship between students
and courses.

That third table, at a minimum, would only need a StudentID (from the
student table) and a CourseID (from the course table) to show valid
combinations.

Now your search for students in the students table who do not have a record
in the StudentCourse (resolver) table could be handled by first finding all
students who DO have a row for that course, then using the results of that
first query to find unmatched students from the student table when compared
to the first query (i.e., who on the list is NOT on the list of students who
TOOK the course).