I'll present a simple senario to illustrate the solution I'm looking for. Take three tables: (1) PARENT tblPerson(PersonID, PersonLastName, ...), (2) CHILD tblSubject(SubjectID, PersonID, SubjectName), and (3) LOOKUP tblLookupSubject(SubjectName). For SubjectName in tblSubject there is a combo box lookup to tblLookupSubject (say this lookup table contains 5 subjects).
A person can take multiple subjects. When adding the first record for a person the combo box displays all 5 subjects. When adding a second subject for a person the combo box still displays all 5 subjects.
How do I get the combo box to display only the subjects that have not been previously selected? (That is, after one subject has been selected the combo box should display the remaining 4 subjects, etx.)
What I have now are two queries:
qryA(PersonID,SubjectName) - lists every possible subject for every person
qryB(PersonID,SubjectName) - lists just what subjects have actually been selected by different persons
What I really need is a query that lists every record in qryA EXCEPT those records in qryB where the PersonIDs match. This will then give a list of which subjects a given person has not taken.
I've tried different types of joins, but cannot get any to work.
I have a solution, although probably not the most elegant.
What I did was to modify qryA & qryB (above) each to a single, concatenated, field “PersonIDubjectName” (after converting the autonumber PersonID to a string; “:” is used as a placement character to later separate PersonID and SubjectName.)
I then used an SQL statement of the form provided by Nick above to get the “PersonIDubjectName” records NOT yet selected, and then used another query to split “PersonIDubjectName” back to PersonID, SubjectName (converting the string PersonID back to an integer.)
I used this final query as the source for the combo box on the form using the criteria that the PersonID on the form must match the PersonID from the query.
Thanks for the help which pointed me in the right direction.