Help with an Access query: I thought this was going to be easy!

[Juddy58]

Hi, Im having trouble with a query. In an employee database im working on. There are two tables im working with:
tblSkillPositionRequiredSkill
fldPositionID fldSkillID
1 24
1 25
2 24
2 40
2 41
This table shows what skills are needed for a particular position. eg for position 1 skills 24 and 25 are required.

The other table is tblSkillEmployee
fldEmployeeID fldSkillID
4 24
5 39
6 24
6 25
7 20
9 24
9 25
This table shows what employees have what skills.
What im trying to do is create a query to show all employees who have all the required skills for
a selected position. So for the example if the parameter for position was 1 I would get Employees 6 and 9. As they have the all of skills required for position 1.
I can't seem to get my head around how to do this. At first I thought it was going to be easy!
Any advice would be greatly appreciated.
Thanks
Justin

 

[BNPMike]

Quick thought here: If you join on fldSkillID you can use a count subquery to get the number of unique skills (2 in the example) then you can group on employees and pick the groups having a count of two. That should select the people with both required skills.

 

[PHV]

A starting point:
SELECT P.fldPositionID, E.fldEmployeeID
FROM tblSkillPositionRequiredSkill AS P INNER JOIN tblSkillEmployee AS E ON P.fldSkillID = E.fldSkillID
GROUP BY P.fldPositionID, E.fldEmployeeID
HAVING Count(*) = (SELECT Count(*) FROM tblSkillPositionRequiredSkill WHERE fldPositionID=P.fldPositionID)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BNPMike]

That's why PHV always gets guru of the week and I don't...

 

[lespaul]

It's very humbling, isn't it, Mike?? I always hate posting an answer KNOWING that there will be a simultaneous posting by PHV that will show me just how WRONG I am!!!!

But I keep trying! Someday.........
Les

 

[BNPMike]

If Bill Gates doesn't leave a considerable amount of money to him in his will, I for one will be straight on the blower...

 

[Juddy58]

Thanks for the suggestions everyone(expecially BNPMike and PHV). I will have a go in the next day or two and let you know how I go.
Cheers
Justin

 

[PHV]

If Bill Gates doesn't leave a considerable amount of money to him
If for you, him is me, please read the first line of my member's profile ...