Multiple values comparison.

[ryainad]

Hi there,



I got 3 tables called Skills, Employee and Position. Tables Employee and Position have Skills column which is multiple valued.
I need to fetch every employee who has set of skills that is required in given Position. What I am trying to do is to compare set of skills in Employee and Position and select employees with full match of skills.

Below, I attached my ERD diagram of database

[June7]

Review this article http://office.microsoft.com/en-us/ac...33722.aspx#BM7

[ryainad]

Review this article http://office.microsoft.com/en-us/ac...33722.aspx#BM7

Thank you for the advice.
I've read the article, but still dont know how to make the query.
What I need is to enter any Position and get every Emloyee, who has skills that are required for that Position.

Now, I have this query to get Position to know what skills are required:
[Position]![Position Name]=[Enter Position]
But I don't know how to fetch not only Employees who has that Position, but those who has skills of that Position.

[recyan]

Not the answer to your question.
Do not know if I have understood things correctly :

1) one SKILL can be associated with many POSITION.
2) One POSITION can be associated with only one SKILL.
3) One EMPLOYEE can be associated with only one POSITION.
4) One POSITION can be associated with many EMPLOYEES.
5) One EMPLOYEE can be associated with only one SKILL.
6) One SKILL can be associated with many EMPLOYEES.

Am a bit puzzled as to why SKILL table is associated with POSITION and EMPLOYEE tables directly.

Thanks

[June7]

Position table shows skills requireded, Employee table shows skills employee has? Could employee have skills not required by position, or not have skills required by position?

I don't use multi-value fields. Could you provide project? Make copy, remove confidential data, run Compact & Repair, zip if large, attach to post.

[ryainad]

Hi, thank you for response.

1) one SKILL can be associated with many POSITION. - YES
2) One POSITION can be associated with only one SKILL. (not exactly one Position can be associated with multiple value of Skills, i.e. with many SKILLs)
3) One EMPLOYEE can be associated with only one POSITION. - YES
4) One POSITION can be associated with many EMPLOYEES. - YES
5) One EMPLOYEE can be associated with only one SKILL. (not exactly one EMPLOYEE can be associated with multiple value of SKILLs, i.e. with many SKILLs)
6) One SKILL can be associated with many EMPLOYEES. - YES

SKILL table is associated with POSITION and EMPLOYEE tables directly, because EMPLOYEE may have set of SKILLs different from POSITION set of skills.

I hope its clear. If you have more question I'll try to answer.

Thanks.

[ryainad]

Position table shows skills requireded, Employee table shows skills employee has? Could employee have skills not required by position, or not have skills required by position?

I don't use multi-value fields. Could you provide project? Make copy, remove confidential data, run Compact & Repair, zip if large, attach to post.

Yes, Position table shows skills requireded, Employee table shows skills employee has. Yes, Employee can have not required by Position, but Employee has to have all Skills required by Position.

I need to create a query to find all Employees who has Skills, required by Position. This query will be used in case if we have an available Position and we need to find Employee who may be promoted or upgraded with higher Position.

Thanks

[June7]

Try this series of queries:

EmployeeSkills
SELECT Employee.EmployeeID, Employee.FullName, Employee.skills.Value
FROM Employee;

PositionSkills
SELECT Position.Position_ID, Position.[Position Name], Position.skills.Value
FROM [Position];

SkillsPosEmp
SELECT PositionSkills.[Position Name], EmployeeSkills.FullName, Skills.Skill_Name, "X" AS Hash
FROM Skills INNER JOIN (PositionSkills LEFT JOIN EmployeeSkills ON PositionSkills.Position.skills.Value = EmployeeSkills.Employee.skills.Value) ON Skills.Skill_ID = PositionSkills.Position.skills.Value
ORDER BY PositionSkills.[Position Name];

Matrix
TRANSFORM First(SkillsPosEmp.Hash) AS FirstOfHash
SELECT SkillsPosEmp.PositionSkills.[Position Name], SkillsPosEmp.EmployeeSkills.FullName
FROM SkillsPosEmp
GROUP BY SkillsPosEmp.PositionSkills.[Position Name], SkillsPosEmp.EmployeeSkills.FullName
PIVOT SkillsPosEmp.Skill_Name;

[ryainad]

Thanks, its working.