Query to match job and client skills?

[kbp]

Hi there hope someone here can help me??

Slowly losing my mind, i'm in no way a programmer or database engineer, but i've been asked to produce a database, which closely resembles an employment agency.

I've created a table for [ clients ] and a table for [ jobs ].

Each jobs may require many skills of an expected employee, and also a client could have many skills.



so i created a table of [ skills ],
and then a table of [ job skills ] and [ client skills ]

The [ job skills ] table contains the fields (skills_id) and (job_id) similiary then the [ client skills ].

so the tables are linked like this--

[client] --< [client_skills] >-- [skills] --< [job_skills] >-- [job]

i hope you can understand my one-to-many relationships??

my major problem is that i want to produce a list of clients that are related to jobs based on their skills.

I have created a query that brings back a list of clients that are related to jobs, it brings back the all of the clients that have 1 skill that is required! but i want it to narrow down the search so that it only brings back the clients that have all of the skills required of the job.

does this make sense to anyone??

[jzwp11]

This will probably take a series of queries to get to a solution.

First, I would create 2 simple queries. The first query just brings together the job and the skills required for it.

query name:qryJobSkills
SELECT tblJobs.pkJobID, tblJobSkills.fkSkillID
FROM tblJobs INNER JOIN tblJobSkills ON tblJobs.pkJobID = tblJobSkills.fkJobID;


Then do the same for the clients and their skills:
query name: qryClientSkills
SELECT tblClients.pkClientID, tblClientSkills.fkSkillID
FROM tblClients INNER JOIN tblClientSkills ON tblClients.pkClientID = tblClientSkills.fkClientID;


Now create a third query that joins the two above queries. You want all of the job skills that must be matched so this will require a LEFT JOIN


query name: qryMatchingSkills
SELECT qryJobSkills.pkJobID, qryClientSkills.pkClientID, qryJobSkills.fkSkillID
FROM qryJobSkills LEFT JOIN qryClientSkills ON qryJobSkills.fkSkillID = qryClientSkills.fkSkillID;


The above query shows records of people who have a least one matching skill but of course we want the client who match all skills of the job, so now we have to do some counting

So we need to count the number of skills required for the job

query name: qryCountOfSkillsNeeded
SELECT qryJobSkills.pkJobID, Count(qryJobSkills.fkSkillID) AS CountOffkSkillID
FROM qryJobSkills
GROUP BY qryJobSkills.pkJobID;


...and we need to count the matched skills of each client

query name: qryCountOFSkillsMetByClient
SELECT qryMatchingSkills.pkJobID, qryMatchingSkills.pkClientID, Count(qryMatchingSkills.fkSkillID) AS CountOffkSkillID
FROM qryMatchingSkills
GROUP BY qryMatchingSkills.pkJobID, qryMatchingSkills.pkClientID;


Then the final query needs to join the two counting queries by both the jobID field and the count field (since the clients must have at least the same number of match skills as that required by the job). I also brought in the client and job tables to bring in the appropriate names rather than just displaying the ID fields


query name: qryClientsMeetingAllJobSkillRequirements
SELECT qryCountOFSkillsMetByClient.pkJobID, qryCountOFSkillsMetByClient.pkClientID, tblJobs.txtJobName, tblClients.txtLName, tblClients.txtFName
FROM ((qryCountOFSkillsMetByClient INNER JOIN qryCountOfSkillsNeeded ON (qryCountOFSkillsMetByClient.pkJobID = qryCountOfSkillsNeeded.pkJobID) AND (qryCountOFSkillsMetByClient.CountOffkSkillID = qryCountOfSkillsNeeded.CountOffkSkillID)) INNER JOIN tblClients ON qryCountOFSkillsMetByClient.pkClientID = tblClients.pkClientID) INNER JOIN tblJobs ON qryCountOFSkillsMetByClient.pkJobID = tblJobs.pkJobID;



I've attached a sample database that includes all of the above queries.

[kbp]

Awesome, you are awesome!!!!

Thank you so much, i'll have a look through that tomorrow, and see if i can get it working.

I also wanted to give the thread a meaningful title so that people will know what its about, if they are searching to solve a similar problem! What do you think, i'm not even sure what i asked..

Cheers again

[jzwp11]

The title just came through as Query Help; maybe something like Query to match job and client skills?

Please let us know if you are successful in adapting the example to your database.

[kbp]

Yeah, got it all working swimmingly!!!

Thanks very much for your help

Andy

[jzwp11]

You're welcome.