Exclude data using fields?

[flushfire]

Ive read the sticky about using tables to exclude data, unfortunately I can't figure out exactly what to do to get the results I want even after reading it. Anyway here's my problem - I want a field in my Enrollment table to lookup data from my Subjects table but exclude data that does not meet the following criteria:
1. The student has enrolled and passed the prerequisites of the subject
2. The student has not already enrolled the subject within the last 6 months

The tables are currently set up like so:

The prerequisite field in the Subjects table use the values from the code field. For example Subject 4 may have the prerequisites Subject 1 & 2 before it can be enrolled.

I can't figure out how to make the query display just the subjects that the student can enroll based on the criteria I mentioned. Any tip would be greatly appreciated. Thanks in advance!

 

[gemma-the-husky]

you dont HAVE to get one simple query

do a query to find the ones that passed
do a query to find the recent enrollees

now do a third query

include all the students
exclude the students in the above two queries. whats left are the ones you want.

so how to exclude some - look at the wizard for unmatched queries, and see how it works.

 

[flushfire]

Thanks for the solution, I'll try that.

Edit: I still have the problem of how to check if the student has already passed the prerequisites before showing the available subjects. Here's a shot of the contents of the Subject table:

I need to filter the subjects available to each students based on what they already enrolled and passed, according to the contents of the Prerequisite field.

Edit: I guess I could separate the contents of the prerequisite field into two columns so that I wouldn't have problems with the SQL Statement but if there's a way to do it without doing so please tell me thanks.