Queries: Finding all X having all required Y

   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 field:

= (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,

DesiredSkills
SkillID
"Is Animated"
"Has Sounds Effects"

CandidatesSkills
"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.

DesiredSkills
SkillID, Weight
"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 analysis!