Impossible SQL problem!

[Coolboy55]

Goal: Select all employees that have all skills necessary to complete a task.

Relevant tables and (fields): Employees (EmployeeID, EmployeeName), Employees/Skills (EmployeeID, SkillID), Tasks/Skills (TaskID, SkillID)

Relevant forms: Tasks

Relevant relationships: Employees to Skills is Many-to-Many, Tasks to Skills is Many-to-Many

Goal restated: Populate combobox TaskCompletedBy on form Tasks with all EmployeeNames from table Employees where [Employees/Skills].[SkillID] is a subset of [Tasks/Skills].[SkillID].

Example: Employee1 has Skill1, Skill2, and Skill3. Task1 requires Skill2 and Skill3. Combobox is populated with Employee1, since his skillset is a subset of the required skillset.

Can anyone help me out? This SQL statement is going to give me a stroke.

CB55

[Stewart Ross]

...Relevant relationships: Employees to Skills is Many-to-Many, Tasks to Skills is Many-to-Many
...

Before you even attempt to write your query you must resolve the table design into a normalised form. Your many-to-many relationships must be resolved - you have no chance of writing anything useful until you do. This means creating link tables that resolve the many-to-many into two one-to-many relationships in each case.

There is a useful article in the HowTo section of the forum: see Database Normalisation and Table Structures.

-Stewart

[Coolboy55]

Before you even attempt to write your query you must resolve the table design into a normalised form. Your many-to-many relationships must be resolved - you have no chance of writing anything useful until you do. This means creating link tables that resolve the many-to-many into two one-to-many relationships in each case.

There is a useful article in the HowTo section of the forum: see Database Normalisation and Table Structures.

-Stewart

Sorry, I was unclear in my original post. I have link tables already with names Employees/Skills and Tasks/Skills with the fields I listed.

[FishVal]

Hi, there.

Your design is still unclear.
Please post the tables metadata, particulary what concerns relationships.
Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK (table name 1)
Name; String
University; String; FK (table name 2)
Mark; Numeric
LastAttendance; Date/Time

Regards,
Fish

[FishVal]

Ok.

I think the following may be a solution.
It seems to work, though not thoroughly tested.
The naming is slight not the same, but I guess quite recognizable.

qryEmpSkills

SELECT tblEmps.keyEmpID, tblEmps.txtEmpName, tblEmpsSkills.keySkillID
FROM tblEmps INNER JOIN tblEmpsSkills ON tblEmps.keyEmpID = tblEmpsSkills.keyEmpID;
 

qryTaskSkills

SELECT tblTasks.keyTaskID, tblTasks.txtTaskName, tblTasksSkills.keySkillID
FROM tblTasks INNER JOIN tblTasksSkills ON tblTasks.keyTaskID = tblTasksSkills.keyTaskID;
 

And the query doing the job.

SELECT qryEmpSkills.keyEmpID, qryEmpSkills.txtEmpName
FROM qryEmpSkills RIGHT JOIN qryTaskSkills ON qryEmpSkills.keySkillID = qryTaskSkills.keySkillID
WHERE (((qryTaskSkills.keyTaskID)=<.... required Task ID goes in here ...>))
GROUP BY qryEmpSkills.keyEmpID, qryEmpSkills.txtEmpName
HAVING (((Count(qryEmpSkills.keySkillID))=(SELECT Count(qryTaskSkills.keySkillID) FROM qryTaskSkills WHERE qryTaskSkills.keyTaskID=<.... required Task ID goes in here too ...>)));
 

Regards,
Fish

[Coolboy55]

Ok.

I think the following may be a solution.
It seems to work, though not thoroughly tested.
The naming is slight not the same, but I guess quite recognizable.

qryEmpSkills

SELECT tblEmps.keyEmpID, tblEmps.txtEmpName, tblEmpsSkills.keySkillID
FROM tblEmps INNER JOIN tblEmpsSkills ON tblEmps.keyEmpID = tblEmpsSkills.keyEmpID;
 

qryTaskSkills

SELECT tblTasks.keyTaskID, tblTasks.txtTaskName, tblTasksSkills.keySkillID
FROM tblTasks INNER JOIN tblTasksSkills ON tblTasks.keyTaskID = tblTasksSkills.keyTaskID;
 

And the query doing the job.

SELECT qryEmpSkills.keyEmpID, qryEmpSkills.txtEmpName
FROM qryEmpSkills RIGHT JOIN qryTaskSkills ON qryEmpSkills.keySkillID = qryTaskSkills.keySkillID
WHERE (((qryTaskSkills.keyTaskID)=<.... required Task ID goes in here ...>))
GROUP BY qryEmpSkills.keyEmpID, qryEmpSkills.txtEmpName
HAVING (((Count(qryEmpSkills.keySkillID))=(SELECT Count(qryTaskSkills.keySkillID) FROM qryTaskSkills WHERE qryTaskSkills.keyTaskID=<.... required Task ID goes in here too ...>)));
 

Regards,
Fish

Ahh! Thank you so much! :D It works perfectly! I was trying to pile it all into one query and never even thought to split it up. Even though I phrased my question incorrectly, your solution was the correct one for the right question. I had said that the employee's skillset should be a subset of the required skills for the task, when of course it was the other way around. Here was my solution for the wrong question:

 
SELECT DISTINCT [Employees].[EmployeeName] FROM Employees INNER JOIN [Employees/Skills] ON [Employees].[EmployeeID]=[Employees/Skills].[EmployeeID] 
WHERE [Employees/Skills].[SkillID] IN (SELECT [Tasks/Skills].[SkillID] FROM [Tasks/Skills] WHERE [TaskID]=[Forms]![Tasks]![SUB - FMEA Tasks]![TaskID])
ORDER BY [Employees].[EmployeeName];
 
 

There probably is a way to write the query in one line, but I'm not going to try. :) Thanks again!

CB55

[FishVal]

You are welcome.
Good luck.