Complicated Query

[Don]

i have orders which have different requirements, for example

order 1 = requirement1

order 2 = requirement1
requirement2
requirement3

order 3 = requirement2
requirement3

order 4 = requirement2

and i also have employees that have different expertise.

employee 1 = expertise1

employee 2 = expertise2
expertise3

employee 3 = expertise1
expertise3

employee 4 = expertise1
expertise2
expertise3


i would like to create a query which gives me a list which employees is able
to complete which order. the expertise fields and the requirements fields
have the same attributes, for instance, by looking at the above example, we
can see that: -

employee 4 = all orders
employee 1 = order 1
employee 2 = order 2 & 3
employee 3 = none

is it possible to create a query that can do this. employee may have more
then one field for expertise and an order may have more then one field for
requirements. I can get my head round how to do it. Please can somebody help
me. If the information is too simple, please ask. i appreciate that you have
taken the time to read this email.

Thank you
don

 

[Ken Snell [MVP]]

I assume that you have a junction table that relates employees to
requirements/expertises:

tblEmpExpertise
EmployeeID
ExpertiseID

Assuming that ExpertiseID holds a number such as 1, 2, 3, etc., use a query
similiar to this (I've hardcoded in the value of 1 as the requirement
value -- you can replace this with whichever value you wish):

SELECT EmployeesTable.EmployeeID, EmployeesTable.EmployeeName
FROM EmployeesTable INNER JOIN tblEmpExpertise
ON EmployeesTable.EmployeeID = tblEmpExpertise.EmployeeID
WHERE tblEmpExpertise.ExpertiseID = 1;

 

[Don]

how can i use this to find which employee is able to complete which order. is
there a way that i could improve this to find this information. what if the
order requires expertise and there is is only 1 employee that has 3
expertises.

thanks

 

[Ken Snell [MVP]]

This would be done by using a query that contains the employee table joined
to multiple copies of the junction table. This will best be done by
programming, essentially building the query based upon how many expertises
are needed for a single order.

As an example, let's assume that your order needs experises 1 and 4. In
general, the SQL statement for finding the employees who have these two
expertises would be this:

SELECT tblEmployee.EmpID, tblEmployee.EmpName
FROM (tblEmployeeINNER JOIN tblEmpExpertises ON
tblEmployee.EmpID = tblEmpExpertises.EmpID) INNER JOIN
tblEmpExpertises AS tblEmpExpertises_1 ON
tblEmployee.EmpID = tblEmpExpertises_1.EmpID
WHERE (((tblEmpExpertises.ExpID)=1) AND
((tblEmpExpertises_1.ExpID)=4))
GROUP BY tblEmployee.EmpID, tblEmployee.EmpName;

You would need to expand on this if you had three expertises or more by
adding another copy of the tblEmpExpertises table with an alias and joining
it to tblEmployees.
--

Ken Snell
<MS ACCESS MVP>