By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,547 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Impossible SQL problem!

P: 67
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
Mar 20 '08 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
...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
Mar 20 '08 #2

P: 67
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.
Mar 20 '08 #3

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK (table name 1)
  4. Name; String
  5. University; String; FK (table name 2)
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Regards,
Fish
Mar 20 '08 #4

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. SELECT tblEmps.keyEmpID, tblEmps.txtEmpName, tblEmpsSkills.keySkillID
  2. FROM tblEmps INNER JOIN tblEmpsSkills ON tblEmps.keyEmpID = tblEmpsSkills.keyEmpID;
  3.  
qryTaskSkills
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTasks.keyTaskID, tblTasks.txtTaskName, tblTasksSkills.keySkillID
  2. FROM tblTasks INNER JOIN tblTasksSkills ON tblTasks.keyTaskID = tblTasksSkills.keyTaskID;
  3.  

And the query doing the job.
Expand|Select|Wrap|Line Numbers
  1. SELECT qryEmpSkills.keyEmpID, qryEmpSkills.txtEmpName
  2. FROM qryEmpSkills RIGHT JOIN qryTaskSkills ON qryEmpSkills.keySkillID = qryTaskSkills.keySkillID
  3. WHERE (((qryTaskSkills.keyTaskID)=<.... required Task ID goes in here ...>))
  4. GROUP BY qryEmpSkills.keyEmpID, qryEmpSkills.txtEmpName
  5. HAVING (((Count(qryEmpSkills.keySkillID))=(SELECT Count(qryTaskSkills.keySkillID) FROM qryTaskSkills WHERE qryTaskSkills.keyTaskID=<.... required Task ID goes in here too ...>)));
  6.  
Regards,
Fish
Mar 20 '08 #5

P: 67
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
Expand|Select|Wrap|Line Numbers
  1. SELECT tblEmps.keyEmpID, tblEmps.txtEmpName, tblEmpsSkills.keySkillID
  2. FROM tblEmps INNER JOIN tblEmpsSkills ON tblEmps.keyEmpID = tblEmpsSkills.keyEmpID;
  3.  
qryTaskSkills
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTasks.keyTaskID, tblTasks.txtTaskName, tblTasksSkills.keySkillID
  2. FROM tblTasks INNER JOIN tblTasksSkills ON tblTasks.keyTaskID = tblTasksSkills.keyTaskID;
  3.  

And the query doing the job.
Expand|Select|Wrap|Line Numbers
  1. SELECT qryEmpSkills.keyEmpID, qryEmpSkills.txtEmpName
  2. FROM qryEmpSkills RIGHT JOIN qryTaskSkills ON qryEmpSkills.keySkillID = qryTaskSkills.keySkillID
  3. WHERE (((qryTaskSkills.keyTaskID)=<.... required Task ID goes in here ...>))
  4. GROUP BY qryEmpSkills.keyEmpID, qryEmpSkills.txtEmpName
  5. HAVING (((Count(qryEmpSkills.keySkillID))=(SELECT Count(qryTaskSkills.keySkillID) FROM qryTaskSkills WHERE qryTaskSkills.keyTaskID=<.... required Task ID goes in here too ...>)));
  6.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT DISTINCT [Employees].[EmployeeName] FROM Employees INNER JOIN [Employees/Skills] ON [Employees].[EmployeeID]=[Employees/Skills].[EmployeeID] 
  3. WHERE [Employees/Skills].[SkillID] IN (SELECT [Tasks/Skills].[SkillID] FROM [Tasks/Skills] WHERE [TaskID]=[Forms]![Tasks]![SUB - FMEA Tasks]![TaskID])
  4. ORDER BY [Employees].[EmployeeName];
  5.  
  6.  
There probably is a way to write the query in one line, but I'm not going to try. :) Thanks again!

CB55
Mar 20 '08 #6

FishVal
Expert 2.5K+
P: 2,653
You are welcome.
Good luck.
Mar 20 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.