Compare tables in Microsoft Access and EXCLUDE duplicate fields?

[Access2010Newbie]

Hi All,

I'm new to Access, and I've created a test database to learn with.
What I created was a student database for a school. It's all been going very well up till now, but I'm stuck and was wondering if anyone here might be able to point out a solution.


I'd like to compare two tables and EXCLUDE the duplicate records in the result.
That's all. Simple, right?

More info:

I have a "ClientInfo table, containing one student's contact info, and I've linked that to a separate "Transcripts" table, that shows the classes they've completed. They’re linked with the “Student ID Number” field.)


The info from both those tables show up as two separate tabs in a form I’ve created called “StudentInfo”.

Then I have a third table called “Classes”, listing all the classes the school would provide.


I want to compare the classes the student's already completed against the classes offered, and have the result be only the classes that they haven't taken.
That way, you could tell a student what classes are available to them in the next quarter.

As I said, compare two tables and EXCLUDE the duplicate records in the result.



I’ve created a query, and looked through and tried building a bunch of different types of expressions in the expression builder, but I can’t figure out the expression to exclude matching data between the two tables: The one with the Student ID number and the classes they’ve taken (Transcripts), and the “Classes” table, containing a list of all the classes offered.

I figured I could run a query using the "ClassName" field, which appears in both the "Transcripts" and "Classes" tables. I've been searching online for hours, went to the Microsoft site, looked through a bunch of their help and training videos, but can only find examples to INCLUDE all matching records between two tables, I dunno, maybe I'm phrasing my searches incorrectly?

I would imagine this is pretty simple stuff, just find any existing class name in the student’s “Transcripts” table, match it against any class name in the “Classes” table, and exclude any data that matches, like I said, seems simple, but I'm stuck.

I’d greatly appreciate any suggestions.
If there’s any additional info needed, I’ll be happy to provide it.
Thanks in advance for any help!


I cleared out any expressions I'd tried, to clean it up, and took a screenshot of what I have now:

[June7]

Did you try the FindUnmatched query in the Query Wizard?

[Access2010Newbie]

Did you try the FindUnmatched query in the Query Wizard?

Thanks for your reply, and yes, I did try that. I ran the wizard and pasted the simple "Is Null" argument it created into the Query I had previously created and just got no fields in the result. An empty result.

See, the "Transcript" table would be all of the classes taken by all of the students, so it would contain all classes, just like the "Classes" table would.

I think maybe I have to put each student's transcript info into the same table as their personal info. Seems like the simplest way to do this.
I had wanted to keep their info and transcripts separate, but putting them in the same table seems like the simplest solution. I'll make a copy of the database, so I have both versions to play with, and try that out...if anyone else has any ideas, please feel free to comment!

Thanks again though for replying!

[June7]

Would have to know more about your data structure to advise further. If you want to provide your project, attach to post. Make copy, remove confidential data, run Compact & Repair, zip if still large.