Search The Web

Today's Headlines

Monday, April 5, 2010

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.

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.

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:

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:

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!

Search The Web

Made Possible By The Generous Donations Of Readers Like You

If you like the content of this blog, please consider a donation to keep it going. This blog is also supported by my sponsors to whom I am ever so grateful. You can visit my sponsors' websites without leaving this page by holding down the "Ctrl" key while clicking on the sponsors' messages. Thank you!

Read This Blog In Your Language

Double-click on any word in this blog to get a dictionary definition of that word in a new window, courtesy of Free Online Dictionary. You can also look up any word (including words in several languages other than English) using the Dictionary Lookup element below.

Jump To A Random Post

Popular Pages

Recommended Reading

Live Traffic Feed

Live Traffic Map

Visitor Countries

Visitor States

Locate IP Address

Blog Exchange

Website Tracking and Monitoring

Charity Banners

The banners below are not from sponsors or advertisers. These are banners I choose to display on my blog's sidbar to show my support for these charitable organizations and their causes.

If you choose to donate to any of these organizations by clicking on the banners below, such donations DO NOT go through me. I do not handle the donations or get a cut of them. They go in full directly to the organization whose banner you clicked. Thank you for your support of these worthy organizations and causes.

If you have a blog or other website, considering hosting such banners supporting your favorite charities and other causes. These organizations can use all the publicity they can get. Thank you.

Help in the fight against hunger

Support The Red Cross

Wall Against Hunger

Red Cross Safety Classes

Human Rescue Plan

Red Cross Racing

Teaching Hunger?

Make Every Day Earth Day

Red Cross: Change a Life

WFP On The Road

WFP In The Horn Of Africa

Red Cross: Donate Blood

WFP School Meals

Red Cross: Give Life

Disclaimer

This blog features reviews of several products and services. I have not received any compensation for most of these reviews. Some reviews have been paid for by the provider of the products or services. However, all the reviews are completely honest, and reflect my true opinion about the product or service being reviewed.