Compare Data in Multiple Tables

[Access_newbie]

I am having a difficult time comparing data in mulitple tables in Access. The following shows how I have the data set up

Table A: Student Info
Student Name
Address



Table B: Courses Taken
Student Name
Course Taken
Completion Date

Table C: Courses Available
Course
Frequency

I need a query to return Which students have not taken specific courses. The tables are related. How do I join the tables in a query to produce the results I need?

[rpeare]

If this is the actual structure of your tables you're in trouble. Every table should have a primary key. A primary key is a field that can not possibly be duplicated under any circumstances. The way you have it set up if 2 people have the same name you're going to get duplicate results and erroneous reporting. Is this the actual structure of your tables or are you leaving out information?

[Access_newbie]

I have the following keys set up.
Table A: Student Info
Student Name (Primary Key)
Address

Table B: Courses Taken
ID (Primary Key)
Student Name (Foreign Key)
Course Taken (Foreign Key)
Completion Date

Table C: Courses Available
Course (Primary Key)
Frequency

[rpeare]

The student Name as a primary key really needs to be changed to some other field, (an autonumber field would work fine)

if COURSE is a text field like 'english' or 'math' I would also replace that with a autonumber ID field. that way you can change descriptions of your courses without losing any links to other tables.

Once that's done the question is this: are each one of your students required to take each class in your courses? That's what you're basically asking for in your original request. So if you have 500 courses you will want a line in your query for each class the student did not take of those 500, is that really what you want?

[Access_newbie]

Yes. I have about 20 courses that are mandatory. I need to figure out which students have not taken a required course.

[rpeare]

Here's an example

[Access_newbie]

Thanks! I will try it real quick.

[Access_newbie]

It worked!!! Thanks sooo much!!!!