If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Complicated Query
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 |
#2
|
|||
|
|||
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; -- Ken Snell MS ACCESS MVP "Don" wrote in message ... 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 |
#3
|
|||
|
|||
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]" wrote: 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; -- Ken Snell MS ACCESS MVP "Don" wrote in message ... 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 |
#4
|
|||
|
|||
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 "Don" wrote in message ... 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]" wrote: 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; -- Ken Snell MS ACCESS MVP "Don" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Complicated Query | DavidS | Running & Setting Up Queries | 2 | January 28th, 2005 12:59 AM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
Help on a complicated query!? | Access rookie | Running & Setting Up Queries | 10 | January 3rd, 2005 02:55 PM |
Complicated query... Help? | Grahammer | Running & Setting Up Queries | 2 | November 4th, 2004 06:20 AM |
Return repeats info in "8s" | Joy Rose | Running & Setting Up Queries | 14 | October 13th, 2004 10:07 PM |