A quite common question is to get a list of candidates having
all the required skills. A solution proposed some time ago by John L. Viescas allows us to
write a query to answer easily that question.
Create a table, DesiredSkills (on field, SkillID,
indexed, no dup (or primary index)), with one record per skill desired (optional, add a
second field, Weight).
Create another table, CandidatesSkills (two
fields: CandidateID, SkillID; have a compound index on
those two fields, no duplication allowed, or make them a compound primary key).
Bring the two tables in the grid for the design of a new query. Link
(JOIN) both SkillID fields.
Take a view of the data: clearly, the inner join return a
candidateID "n" times, n being the number of desired skill that candidates own.
Go back to the design, push the summation button on the toolbar, to
get the new line "Total" in the grid. Drag the field CandidateID in the grid,
keep the GroupBy proposed selection. Bring DesiredSkills.SkillID in the grid, change the
GroupBy to a Count.
Take a view of the data and note that this count return the value
"n" discussed before. If there is "m" desired skills, we want only
those records where n = m.
Go back to the design, add, in the criteria line, under SkillID
= (SELECT Count(*) FROM DesiredSkills;)
which is simply the value "m".
Look at the data. It should be what you want. Assuming the following
data is present,
"Has Sounds Effects"
"Merlin", "Live in the Past"
"Genie", "Live in the Past"
"Robot", "Live in the Future"
"Merlin", "Is Animated"
"Genie", "Is Animated"
"Robot", "Is Animated"
"Merlin", "Has Sounds Effects"
you are supposed to get only Merlin.
Now, if your criterions are not "absolute", but preferred,
add a Weight field to DesiredSkills table, and, instead of Counting DesiredSkills.SkillID,
SUM the Weight field, and sort it. If you have a mixture of absolute and preferred skills,
keep the Weight field. Sum all the weight of the preferred skills (not considering the
absolute required skills), and just use something a little bit bigger, M, for each
required fields. Sum the weights and add the criteria:
>= (SELECT M*Count(*) FROM DesiredSkills;)
where M is the constant, then, sort them.
"Is Animated", absolutelyReq
"Has Sounds Effects", 0.3
"Live In The Past", 0.25
"Live in the Future", 0.20
Only "Is Animated" is required, so its weight has to be
greater than 0.3+0.25+0.2, here, we take absolutelyReq = 1.00; we get, sorted: Merlin
first (1.55), Genie second (1.25) and Robot (1.20). Since all the preferred requirements,
added together, won't made a sum >= M, we will be sure that only the candidates having
all the absolutely required skills will be listed. Nice start for a multi-criteria