Project Involving Table Relationships

[annemrosenberg]

Hi!



So I've been given this project where I have to develop an access 2007 database where a query can be used to match employees to available projects based on employee skill set and required project skills.

How should I go about this?

I've tried making a bunch of tables and relating them, but its not matching employee skills to project skills!

PLEASE HELP!

Thanks!
Anne

[TheShabz]

A few questions:
Does the employee have to match all of the skills required for the project?
How many employees will be on each project?
Does each project have to have a set number of employees?

[annemrosenberg]

The employee has to match all of the skills.

The project will not have a limit on employees. It will be a method of placing an employee on a current project after his/her project comes to an end.

I tried splitting it into these tables:

Employee names and skills (using a lookup to a seperate skills table)

Projects

Required skills for each project

Skills

I tried using a junction table and it still wouldnt work. I guess I can't visualize how it could even work.

[TheShabz]

I think You will need 5 tables. tblEmployees, which has their information. tblSkills, which contains every skill. tblProjects, which contains all the different projects. tblEmployeeSkills, which will be a junction table for the M-M relationship between Employee and Skills. tblProjectSkills which will be a junction table for Project and Skills. You then query the junction tables for your criteria.

[annemrosenberg]

For the Skills Table would it be possible to use a lookup table for the skills or does each field have to be a different skill with a check box?

[TheShabz]

I don't know why you would use a checkbox. For example:
tblEmployee
EmpID, EmpName
1, John Doe
2, Jane Smith

tblSkills
SkillID, SkillName
1, computer
2, labor
3, math

tblEmployeeSkills
EmpSkillID, EmpID, SkillID
1, 1, 1
2, 1, 2
3, 2, 1
4, 2, 2
5, 2, 3

You don't need any checkboxes.

[annemrosenberg]

Because there are different levels of skill for example

tblSkills Skill_ID Skill ACC1 MS ACCESS (Beginner) ACC2 MS ACCESS (Intermediate) ACC3 MSACCESS (Advanced) ARCGIS1 ESRI ARCGIS (Beginner) ARCGIS2 ESRI ARCGIS (Intermediate) ARCGIS3 ESRI ARCGIS (Advanced) C1 C# (Beginner) C2 C# (Intermediate) C3 C# (Advanced)

[TheShabz]

I don't see why that's an issue. Lets say employee 1 is a beginner at all 3 and employee 2 is advanced in all 3. your junction table would look like:
tblEmployeeSkills
empSkillID, empID, skillID
1, 1, ACC1
2, 1, ARCGIS1
3, 1, C1
4, 2, ACC3
5, 2, ARCGIS3
6, 2, C3

You do the same thing with the project and skills tables and then join the 2 junction tables on the skill and then do a count on how many match. If the total matched = total for the project, that employee has all of the requirements for the project

[annemrosenberg]

Where is empSkillID being pulled from? Or is it just a new field name?

[TheShabz]

It's just an autonumber field used as a primary key. You can choose to use a compound key with empID and skillID if you so wish, I just prefer a separate PK

[annemrosenberg]

Ok in tblProjectSkills table, how Can I assign more than one skill to a project? Can the skills be in one field separated by commas?

[TheShabz]

Nope. You would assign them the same way you gave each employee a skill. Lets say project A required skills ACC1 and C3 and project B required ACC1, ARCGIS2, and C1.

tblProjectSkills
projSkillID, projectID, skillID
1, A, ACC1
2, A, C3
3, B, ACC1
4, B, ARCGIS2
5, B, C1

[annemrosenberg]

Ok see the issue is that I originally assigned skills as a lookup column so each person could have multiple skills for each row.

[annemrosenberg]

BUT, I see How you are doing it, so I'm trying all this now.

[TheShabz]

I avoid lookup tables/fields in general. I don't even know how to query off of one =P