Hi,

Really hoping somebody can help with this.

I have the below tables:

Employee
Employee_ID
First Name
Last Name

Skill
Skill_ID
Skill_Name

Qualification
Qualification_ID
Qualification_Name

Technology
Technology_ID
Technology_Name

Sector
Sector_ID
Sector_Name

I assume they are all many to many relationships with employee so am I right to assume they all require seperate link/junction tables to connect to employee? So for example:

Employee_Skill

Employee_ID
Skill_ID

and the same for the other tables.

If this is correct, how do I then run a query to show me all data? I have tried this (with junction tables) and the data is duplicated lots of times. Would I then require a union join?

Key questions are:

1. What is the best way to join the above tables?
2. How do I then run a query that shows me all attributes (skill, qualification, sector, technology) linked to an employee without it duplicating data?

Thanks a lot for your help!

Paul

You just want to use outer or left joins.

Paste this into a query

SELECT Tbl_Employee.Name, [TBL EMP_SKILL].SKILL_ID, Tbl_Emp_Qualification.Qualification_ID, Tbl_Emp_Technology.Tech_ID
FROM ((Tbl_Employee LEFT JOIN [TBL EMP_SKILL] ON Tbl_Employee.EMP_ID = [TBL EMP_SKILL].EMP_ID) LEFT JOIN Tbl_Emp_Qualification ON Tbl_Employee.EMP_ID = Tbl_Emp_Qualification.EMP_ID) LEFT JOIN Tbl_Emp_Technology ON Tbl_Employee.EMP_ID = Tbl_Emp_Technology.EMP_ID;

Then you link your skill table, qual table etc to their ids in the bridge table in your query

I suspect the problem is that one Employee can have several Skills, and also have several Qualifications - but that there is no relationship between Skills and Qualifications. If you try to join all the tables in One Big Master Query you'll have a problem, because you will have no join between (say) EmployeeSkills and EmployeeQualifications; you'll get one record for every combination of the two.

But... why do you feel that you need One Big Master Query? Normally one would have a Form (or Report) based on Employees, with Subforms (or subreports) based on the junction tables, avoiding the duplication.

I see what you're saying about the query issue...pulling all that info in one query at once seems to be overkill. However, as far as recording the information goes, wouldn't you want to have relationships between each of those descriptors? What I mean is that QualificationA may have prerequisites of SkillA & SkillB but QualificationB has prerequisites of SkillC & SkillD (or vice versa, depending on how the OP defines the terms). It seems that each of those terms (Skills, Qualifications, Technology, Sectors) could be affected by the others. I would think the possibility exists that you could have problems in the future with how the data was recorded if this isn't accounted for now. Would it be too much to add another level of complexity to this so that these relationships are accommodated also?

Well, your database design should reflect the real-life situation you're trying to model. If skills and qualifications are interdependent and your task requires modeling and tracking those interdependencies, then yes, you'll need another junction table related one-to-many to both skills and to qualifications. How that would be connected to employees isn't altogether clear to me this time of night...