MS Access SQL that shows courses NOT TAKEN by student before

Question

I have tried many ways, and i suspect NOT EXIST may help, but i am not sure how to implement the SQL statement. Someone help me please?

I have three tables in MS Access database.

Students (Student Name, Email)
Courses (Course Name, Fees)
Registration (Course Name, Student Name)

I want to now create a Query that can show me all classes that each student HAS NOT taken before. This will allow my Registration Manager to pursue them to perform more class registration.

How can I easy do this in MS ACCESS? The resulting query should be:

James|Advanced Flash
James|Advanced Editing
Adrian|Basic Editing
Adrian|Basic Flash
Adrian|Advanced Flash
Adrian|Advanced Editing

(James have taken all 'Basic' classes where else Adrian has not taken 'Basic' and 'Advanced' classes)

Answer 1

The idea is to create all combines of students and courses. Then do a left join to Registrations. Anything that does not match is a course not taken.

Access syntax is a bit different from other databases. I think the following is the Access syntax for this type of query:

select s.studentName, c.CourseName
from ((select studentName
       from students
      ) s,
      (select CourseName
       from courses
      ) c
     ) left join
     Registration r
     on r.StudentName = s.StudentName and
        r.CourseName = s.CourseName
where r.StudentName is NULL;

EDIT:

I think the parens might have to be ordered correctly for Access to understand the joins. In any case, the following should work. The driver subquery gets all combinations of students and courses, which is then used for the left join:

select driver.StudentName, driver.CourseName
from (select distinct StudentName, CourseName
      from students, courses
     ) driver left join
     Registration r
     on r.StudentName = driver.StudentName and
        r.CourseName = driver.CourseName
where r.StudentName is NULL;