help with subqueries

[Guest]

I have 3 tables, Technicians, Tasks, and Procedures. Each procedure has one
or more tasks required for performance. Each task can be associated with more
than one procedure. A technician can have only one of each qual level for a
task, i.e., none, classroom trained, on-job-trained, and qualified. So, a
technician is qualified to perform a given procedure only if he is fully
qualified on all tasks associated with that procedure.

My current design has a join table between Procedures and Tasks, and a join
table between Task and Technicians, with a tblTech-Task-Join.QualLevel field
to record what qual level the tech is qualified for that task.

tblProcedures tblProc-Task-Join tblTasks tblTech-Task-Join
tblTechs
\1-many/ \many-1/ \1-many/
\many-1/

How can I design a query/subquery that, for a given procedure, returns a
list of technicians who are only fully qualified?

 

[Guest]

oops, that should read:

tblProc tblProc-Task-Join tblTasks tblTech-Task-Join tblTechs
\1-many/ \many-1/ \1-many/ \many-1/

 

[Gary Walter]

Hi Mike,

In a very general sense, I would try

SELECT ProcName, TechName

FROM "joining all the tables"

WHERE ProcName='someproc'

GROUP BY ProcName, TechName

HAVING Min([QualLevel]<>'qualified')=0

(that would assume each tech has a QualLevel
for each task?)

If a tech fails to be fully qualified for any task,
min will be -1 and tech will be rejected by Having
clause.

If a tech is fully qualified for all tasks,
min will 0 and Having clause will include
that tech in records returned.

Hope not too general...

good luck,

gary

 

[Gary Walter]

could just as easily used
(if I'm thinking correctly)

HAVING Max([QualLevel]='qualified') < 0

true = -1
false = 0

if any [QualLevel] was not 'qualified'
for a specific tech. Max would be 0

if all [QualLevel] were 'qualified'
for a specific tech, Max would be -1

In a very general sense, I would try

SELECT ProcName, TechName

FROM "joining all the tables"

WHERE ProcName='someproc'

GROUP BY ProcName, TechName

HAVING Min([QualLevel]<>'qualified')=0

(that would assume each tech has a QualLevel
for each task?)

If a tech fails to be fully qualified for any task,
min will be -1 and tech will be rejected by Having
clause.

If a tech is fully qualified for all tasks,
min will 0 and Having clause will include
that tech in records returned.

Hope not too general...

good luck,

gary

oops, that should read:

tblProc tblProc-Task-Join tblTasks tblTech-Task-Join tblTechs
\1-many/ \many-1/ \1-many/ \many-1/

 

[Guest]

Gary,
Your first example failed a couple of test cases (but I didn't pursue it),
yet the newer example works in all cases.
Thanks a bunch for your prompt help!
Mike

could just as easily used
(if I'm thinking correctly)

HAVING Max([QualLevel]='qualified') < 0

true = -1
false = 0

if any [QualLevel] was not 'qualified'
for a specific tech. Max would be 0

if all [QualLevel] were 'qualified'
for a specific tech, Max would be -1

In a very general sense, I would try

SELECT ProcName, TechName

FROM "joining all the tables"

WHERE ProcName='someproc'

GROUP BY ProcName, TechName

HAVING Min([QualLevel]<>'qualified')=0

(that would assume each tech has a QualLevel
for each task?)

If a tech fails to be fully qualified for any task,
min will be -1 and tech will be rejected by Having
clause.

If a tech is fully qualified for all tasks,
min will 0 and Having clause will include
that tech in records returned.

Hope not too general...

good luck,

gary

oops, that should read:

tblProc tblProc-Task-Join tblTasks tblTech-Task-Join tblTechs
\1-many/ \many-1/ \1-many/ \many-1/


:

I have 3 tables, Technicians, Tasks, and Procedures. Each procedure has
one
or more tasks required for performance. Each task can be associated with
more
than one procedure. A technician can have only one of each qual level
for a
task, i.e., none, classroom trained, on-job-trained, and qualified. So,
a
technician is qualified to perform a given procedure only if he is fully
qualified on all tasks associated with that procedure.

My current design has a join table between Procedures and Tasks, and a
join
table between Task and Technicians, with a tblTech-Task-Join.QualLevel
field
to record what qual level the tech is qualified for that task.

tblProcedures tblProc-Task-Join tblTasks tblTech-Task-Join
tblTechs
\1-many/ \many-1/ \1-many/
\many-1/

How can I design a query/subquery that, for a given procedure, returns a
list of technicians who are only fully qualified?