MS Access Subquery that returns Multiple Fields

Use LEFT JOIN and look for NULL values:

SELECT, bar.course
FROM bar LEFT JOIN foo ON = AND bar.course = foo.course

I've updated the SQLFiddle to include the INSERT followed by a SELECT to show the final table. I've also added composite primary keys to both tables so you can see that you do not get any duplicate inserts.

your sub query is returning two columns. make it return one. If want a where clause that can be in two columns, use OR

SELECT name, course
FROM foo    
WHERE (name) NOT IN (SELECT name FROM bar) and (course) 
NOT IN (SELECT course FROM bar);


Your problem stems from a normalization issue. A suggested redesign would be to have a table for students and a table for courses and a table to join them. Example:

studentId(int PK)

classId(int PK)


Each student can have many classes and each class can have many students. It's a many to many relationship that is normalized by using the join table.

Now, if you wanted to do a query like your asking:

Select *.student, *.class
studentTable as student,
classTable as class
where<>'allen' and<>'math' 

The query:

SELECT name, course
FROM foo    
WHERE (name, course) NOT IN (SELECT name, course FROM bar);

works in MySQL and Oracle. For Access, and this also works in MySQL and Oracle, you can rewrite it to:

SELECT name, course
FROM foo    
WHERE name NOT IN (SELECT name FROM bar)
AND course NOT IN (SELECT course FROM bar);