Database Logic

[XelaIrodavlas]

Just a simple query from me regarding simple database logic, because I'm still an access newbie...

The setup - I have three tables containing information on ‘Employees’, ‘Job Roles’ and ‘Qualifications’. ‘Employees’ is the parent table, with many to many relationships to both of the other tables. There is currently no direct relationship between ‘Job Roles’ and ‘Qualifications’.

However, for each job role, there can be any number of essential qualifications (in order to get approval), so should I join these tables by many-many relationship as well? If so, can I make a single three-way many to many relationship, or should I keep them separate?

My problem is that there are already other data fields in the relationship table between employees and qualifications (such as the expiry date for that qualification for each employee) which makes sense as it stands, but it would have nothing to do with the job role, so I'm not sure whether joining them as a three way many-many would be logical. But creating a triangle of regular many-many relationships is not something I've done before, so i'm not sure.

The key thing here is that I need to be able to ask the question ‘does the employee have the right qualifications for their role?’ So I need the best way to go about that… Maybe this would be better answered on a form, using an expression perhaps? But I don’t know a lot about that, so please help!

Thanks for your help!

 

[jdraw]

To resolve many to many you use a junction table.

See/watch this free video tutorial
http://www.youtube.com/watch?v=7XstSSyG8fw

You may also wish to consider:

There are qualifications; there are Employees and there are JobRoles.

An Employee may have certain qualifications(skills). A JobRole may require certain Qualifications.

You might consider the question - Does Employee X have the qualifications to make him/her eligible for JobRole Y? Or JobRole W requires qualifications A,B , C and D, which Employees, if any, have those Qualifications?

There is a free data model here that may give you some ideas
http://www.databaseanswers.org/data_models/jobs_skills_and_users/index.htm