MS Access - Linking Tables

ironclaw

Hi Folks,

Apologises if this is the wrong forum, but at the core its a database problem. I've attached a screenshot of the relationships. On a basic level it is a database to query job applications.

On the left I have two tables linked by a Applicant Reference number. In one table is all their details (Name, address etc) and in the second is a set of skills they have. This links up find and I can query these tables.

On the right I have a main job table. This links back to further tables with more detail such as the employer name etc.

What I can't seem to do is link the Applicants with the jobs. So for instance, if a job requires someone to be a Electrician, I can't query. I know what applicants are Electricians but I can't query to the jobs that have the Electrician as skill.

What I need to be able to do is ask the database to return the names of applicants and jobs to suit them i.e. Names of all the Plumbers and available plumbing jobs.

You will note the Applicant Skills table (Extreme left) has the same two fields (Primary and Second Skill) as the Job table.

Any help would be most appreciated!

amen

You appear to be missing.
I hope this is not homewokr

SELECT * FROM
ApplicantTable AppT JOIN
ApplicantSKills AppS
ON Appt.ApplicantRefNumber = Apps.ApplicantRefNumber
JOIN
jobs JB ON
JB.PirmarySkill = Apss.PrimarySkil

should do it.

you should really have a Skills table though

ironclaw

Thank you for getting back to me. Its not homework. Personal study really.

SELECT * FROM
ApplicantTable AppT JOIN
ApplicantSKills AppS
ON Appt.ApplicantRefNumber = Apps.ApplicantRefNumber
JOIN
jobs JB ON
JB.PirmarySkill = Apss.PrimarySkil

How would that work under relations in MS Access? Would I have a relation between the Job Skill and the Applicant Skill?

With regards to a Skills table, how would that link into the general schema?

amen

yes but you should really have a skills table with a unique skillsid and join both to that.