Make sure a person has at least one of each type

[unnamedplayer]

Hello all,

I have a table with a field called TestType. There are three types of tests that can be entered, I'll call them A, B, and C. What I need to know is if there is a way I can query the table to determine who has not taken all 3 tests (and which ones) For example, with a table like below:

Name       TestType
------      --------
Joe         A
Joe         B
Jane        A
Jane        B
Jane        C
Mark        A

I need to show that Joe has not taken test C and Mark has not taken B or C.

Any ideas on how to accomplish this? Thanks!

 

[pbaldy]

Presuming you also have tables with the people and tests, a query with the people table and the tests table without a join will get you the Cartesian product, ie all possible combinations. You could then run the unmatched query wizard with that query and the above table, which should produce your list. The wizard may only let you choose one field to join on, in which case you'd just go edit it and add the other.