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

[i]Field; Type; IndexInfo[/i]
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
[code=sql]
SELECT tblEmps.keyEmpI D, tblEmps.txtEmpN ame, tblEmpsSkills.k eySkillID
FROM tblEmps INNER JOIN tblEmpsSkills ON tblEmps.keyEmpI D = tblEmpsSkills.k eyEmpID;
[/code]

qryTaskSkills
[code=sql]
SELECT tblTasks.keyTas kID, tblTasks.txtTas kName, tblTasksSkills. keySkillID
FROM tblTasks INNER JOIN tblTasksSkills ON tblTasks.keyTas kID = tblTasksSkills. keyTaskID;
[/code]


And the query doing the job.
[code=sql]
SELECT qryEmpSkills.ke yEmpID, qryEmpSkills.tx tEmpName
FROM qryEmpSkills RIGHT JOIN qryTaskSkills ON qryEmpSkills.ke ySkillID = qryTaskSkills.k eySkillID
WHERE (((qryTaskSkill s.keyTaskID)=<. ... required Task ID goes in here ...>))
GROUP BY qryEmpSkills.ke yEmpID, qryEmpSkills.tx tEmpName
HAVING (((Count(qryEmp Skills.keySkill ID))=(SELECT Count(qryTaskSk ills.keySkillID ) FROM qryTaskSkills WHERE qryTaskSkills.k eyTaskID=<.... required Task ID goes in here too ...>)));
[/code]

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
[code=sql]
SELECT tblEmps.keyEmpI D, tblEmps.txtEmpN ame, tblEmpsSkills.k eySkillID
FROM tblEmps INNER JOIN tblEmpsSkills ON tblEmps.keyEmpI D = tblEmpsSkills.k eyEmpID;
[/code]

qryTaskSkills
[code=sql]
SELECT tblTasks.keyTas kID, tblTasks.txtTas kName, tblTasksSkills. keySkillID
FROM tblTasks INNER JOIN tblTasksSkills ON tblTasks.keyTas kID = tblTasksSkills. keyTaskID;
[/code]


And the query doing the job.
[code=sql]
SELECT qryEmpSkills.ke yEmpID, qryEmpSkills.tx tEmpName
FROM qryEmpSkills RIGHT JOIN qryTaskSkills ON qryEmpSkills.ke ySkillID = qryTaskSkills.k eySkillID
WHERE (((qryTaskSkill s.keyTaskID)=<. ... required Task ID goes in here ...>))
GROUP BY qryEmpSkills.ke yEmpID, qryEmpSkills.tx tEmpName
HAVING (((Count(qryEmp Skills.keySkill ID))=(SELECT Count(qryTaskSk ills.keySkillID ) FROM qryTaskSkills WHERE qryTaskSkills.k eyTaskID=<.... required Task ID goes in here too ...>)));
[/code]

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.