Query Tables Where A appears X Times

[limpsharp]

Hi,

I'm at the end of a work project and the final report has got me stumped.

I have a table of employees, a table of modules and a link table of modules and employees and whether or not the employee has passed that module.

There are 21 modules.

I am trying to generate a report which shows each technician if and only if they have "passed" (yes/no field) all 21 modules.

They would then be considered "qualified" . Conversely i want to be able to generate a report if and only if a technician has failed any amount of modules - this would show employees who need further training.

The modules will be further expanded, and i will only be interested in a subset (say all modules beggining with B).

Essentially, I want to show all techs that have passed all modules that begin with B.

Any ideas?

[Stang02GT]

can you give me specific table and field names so that i can try and help you develope an SQL script that will allow you to pull this information?

[limpsharp]

Hi,

Yeah, i didn't give table names originally because i was happy to work with an "abstract" solution, i understand a fair bit about SQL, i'm just not sure what methods to make use of!

I have the following tables i want to show date from and the fields within them:

Areas
mtm
mtmPhone
area [key]

Employees
techID [key]
First Name
Last Name
area [foreign key from areas]

links
element [foreign joint key]
techID [foreign joint key]
passed?

So to recap - i want to only show an employee record once, if he appears in the link table 21 times (abitary number) and each record in the links table holds the value Yes for passed?