Find Missing Classes

[NDBadger]

I have two tables. The first one lists all of the courses that a student has taken. The second table list all available courses. I need a query that will tell me which courses the student has not taken. I tried using the find unmatched query to no success. Any help will be greatly appreciated.

 

[Matt Greatorex]

Something along the lines of

Select * from [course table]
Where [course table].[course code]
Not In (Select [student table].[course code]
From [student table]);

That would give you all courses taken by NO students.
To find those for a particular student:

Select * from [course table]
Where [course table].[course code]
Not In (Select [student table].[course code]
From [student table]
Where [student table].[student id] = whatever the student's ID is);

 

[NDBadger]

I may be missing something. I tried changing the names of the tables in your response to the ones I have and I must be doing something wrong. The first example just gave me a list of the classes.

The two tables are "Course List" and "Student Records". Which one should go where? I appreciate it.

 

[Matt Greatorex]

The first one was split into two parts. The

Select [student table].[course code]
From [student table];

part will provide a list of all codes taken by any students.

The

Select * from [course table]
Where [course table].[course code]
Not In .....

part will give you a list of all information from the table listing the course codes, ONLY for those course codes not appearing in the first list i.e. no students are taking them.

Post your table structures and I'll have another look.

 

[NDBadger]

Here you go.

Student Records:
Last Name First Name Class Title Date Completed Course

Course List:
ID Class Title


Thanks Again

 

[Matt Greatorex]

SELECT [Course List].[ID], [Course List].[Class], [Course List].[Title]
FROM [Course List]
WHERE [Course List].[Class]
NOT IN (SELECT [Student Records].[Class]
FROM [Student Records]
WHERE [Student Records].[Last Name] = '{student's last name}')
AND [Student Records].[First Name] = '{student's first name}');

That should do it, I think.
replace the {} parts with the appropriate information and you get a list of all courses which exists and haven't been taken by whichever student you enter.
Note: the above assumes that the two tables are linked via their Class fields.