Qry Design

[Jason]

I need some help designing a qry. I have an employee table. Each employee is
trained to a certin level. Each level requires some addtional creditentials.
In another table I have those creditentials link my the employee ID. I need a
qry that tells me if an employee trained to level x does not have one or more
of the creditantials. I hope I made sense

 

[Peter Yang[MSFT]]

Hello Jason,

Suppose level-cert-tbl is table for relation between level and certificate
and emp-cert-tbl is for relation between employee and certificate, you may
try to use the following query:

select cert from level-cert-tbl where level=3 and cert not in (select cert
from emp-cert-tbl where emp=empno)

If you have any questions or comments, please feel free to let's know.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[John W. Vinson]

I need some help designing a qry. I have an employee table. Each employee is
trained to a certin level. Each level requires some addtional creditentials.
In another table I have those creditentials link my the employee ID. I need a
qry that tells me if an employee trained to level x does not have one or more
of the creditantials. I hope I made sense

Well, without knowing anything about the structure of your tables it's hard to
be certain... but assuming that you have a Level field in the employees table,
and an EmployeeCredentials table related one to many to Employees, a Not
Exists query will do this:

SELECT Employees.*
FROM Employees
WHERE Employees.Level = x
AND NOT EXISTS
(SELECT EmployeeID FROM EmployeeCertifications
WHERE EmployeeCertifications.EmployeeID = Employees.EmployeeID
AND Certification IN ("A", "B", "C"))