Search The Web

Today's Headlines

Monday, April 5, 2010

Microsoft Access Tips & Tricks: Job Candidate Matching With Skill Levels

In the previous post in this series, we saw how Microsoft Access can be used to match up candidates with jobs based on skills that candidates have, and skills that jobs require. The concept can be used to match up two sets of entities that have availabilities and requirements in common, as mentioned in that post, not just for matching candidates with jobs.

If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields, finding unmatched rows, calculating statistics with grouping and job-candidate matching.

However, we may be interested in matching candidates with jobs based on the skill levels a candidate has in various skills and the required skill level each job requires. So, we assume in this post that having a skill does not automatically qualify a candidate for a job that requires the skill. The candidate must also have a skill level in this skill greater than or equal to the required skill level for this skill specified in the job.

In addition to matching candidates with jobs, this kind of query might be useful to match manufacturing facilities with items to be manufactured. Different items not only require different components, but also certain minimum quantities of each component. Similarly, different manufacturing facilities not only have different components available, but also certain fixed quantities of them. We have to do the matching so that we don't match up an item with a manufacturing facility that has fewer than the required number of any required component for that item.

Just as in the previous post, we will assume that our database contains a JobSkills table and a CandidateSkills. The only difference is that, now these tables include a SkillLevel field. In the case of the JobSkills table, the SkillLevel field specifies the minimum required skill level of that skill for performing the given job. In the case of the CandidateSkills table, the SkillLevel field specifies the candidate's skill level in that particular skill. Our query should then try to match up candidates for each job such that not only do the matched candidates have all the skills required for the job, but also have a skill level for each skill greater than the corresponding required skill level for that skill. We assume that the required skill levels and the candidates' skill assessments follow a common numerical or other scale in which comparisons are easy (numerical skill levels are easiest to use). We will also assume that the greater the skill level required or the greater the skill level a candidate has, the higher the numerical score for the skill level. Thus, a skill level of 5 implies higher skill (either required or available) than a skill level of 3, and so on.

Thus, our JobSkills table might look like the below:
JobID    SkillID    SkillLevel
1 1 1
1 2 4
1 5 2
1 6 3
2 1 3
2 2 3
2 3 5
2 4 3
3 4 2
3 5 4
3 6 2
3 7 4
4 1 2
4 3 2
4 5 2
5 2 4
5 4 4
5 6 4
6 1 5
6 4 1
6 7 3
Similarly, our CandidateSkills table might look as below:
CandidateID  SkillID  SkillLevel
1 1 5
1 2 4
1 4 3
1 6 2
2 1 5
2 3 5
2 5 5
2 7 5
3 2 4
3 4 4
3 6 4
4 1 1
4 2 3
4 3 5
4 5 3
4 6 1
4 7 3
If you have any questions about what the tables fields mean, please refer to the previous post and its descriptions of these tables.

In this case, because of the inclusion of skill levels, we need a query that is a little more advanced than a simple unmatched query. We not only need to make sure that the candidate has all the skills required for a job, but also that the candidate's skill levels in each skill are higher than the corresponding skill levels in that skill required for the job.

This is accomplished using the query below, which illustrates how to check whether Candidate 1 is a good match for Job 1:
SELECT * from JobSkills as JS where JobID = 1 and SkillLevel > all
(select SkillLevel from CandidateSkills where CandidateID = 1
and SkillID = JS.SkillID)
What exactly does this query do? We have an outer query that selects skills corresponding to JobID 1. We then compare the skill levels for each skill required for that job against candidate 1's qualifications. This is accomplished in the subquery that linked to the main query using the ALL clause. Why do we use an ALL clause here instead of ANY or SOME? It turns out that an ALL subquery returns TRUE when the scope of the subquery is empty whereas ANY and SOME don't.

What does that mean? Consider the case where the job requires a skill that the candidate does not have at all. Then the subquery's scope becomes empty because the select statement in the subquery returns no rows (since the candidate does not have SkillID = JS.SkillID). When that happens, ALL returns a TRUE which is then interpreted by the main query as meaning that the job has at least one skill requirement that is not met by the candidate. Using any other type of subquery (ANY or SOME) would return a FALSE when the subquery scope becomes empty, so this query would then imply a good match between Job 1 and Candidate 1 just because Candidate 1 does not have some skill required by Job 1.

So, if the query above returns any rows, it either means that Job 1 requires a skill that Candidate 1 does not have, or Job 1's skill level for a particular skill is higher than Candidate 1's skill level in that skill. Now that we understand how this query works to match up Job 1 with Candidate 1, it is a simple matter to expand the scope so that every candidate is checked against every job. The final query you end up with is shown below:
SELECT JobID, candidateID FROM JobSkills AS J, CandidateSkills AS C
WHERE not exists
(SELECT * from Jobskills as JS where JS.JobID = J.JobID and JS.SkillLevel > all
(select SkillLevel from CandidateSkills
where CandidateID = C.CandidateID and SkillID = JS.skillID))
That is pretty much all there is to it! As you can see, we end up with a triple-nested query structure because of the addition of the outer query to the previous double-nested query we had. Variations of this query to deal with a single JobID or CandidateID should be trivial and readily doable based on the query above.

Hope this post has been helpful in solving any problems you might have had with job-candidate-matching with skill levels type problems in Access. If you have any problems or concerns with the SQL in this lesson, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future lessons, please feel free to let me know through your comments too. Good luck!

No comments:

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web