Need help designing tables/queries to find qualified candidates for specific job qualifications.

[sagas1]

Hello,



I have business problem where I need to match a large list of candidates with a large list of job roles with a variety of qualifications. For example:

This table defines the qualifications for each role:

Role	Requirement	Requirement Group
Manager	M1	1
Manager	M2	1
Manager	M3	2
Manager	M4	3
Engineer	E1	1
Engineer	E2	2
Engineer	E3	2
Accountant	A1	1
Accountant	A2	1

The table shows the applicants and their qualifications:

Name	Qualification
Ryan	A2
Renee	E1
Renee	E2
Steph	M1
Jason	M1
Jason Jason	M3 M4
Mike	E2

Note that an applicant must meet at least one requirement from each requirement group. So to be a manager the applicant must have ((M1 OR M2) AND M3 AND M4) I would like to be able to query this data to get the following results:

Role	Name	Qualification
Manager	Jason	M1
Manager	Jason	M3
Manager	Jason	M4
Engineer	Renee	E1
Engineer	Renee	E2
Accountant	Ryan	A2

I have full control over the tables, but my challenge is finding a way to query "a candidate that has at least one of each requirement from each requirement group for one role".

Any help would by much appreciated!

[June7]

You have provided very simple datasets as example. I expect reality is more complicated (more than 3 requirements and more than 3 roles). However, I will provide a simple solution for the given data. Consider:

Query1: AllCandRoles
TRANSFORM Count(Candidates.Qualification) AS CountOfQualification
SELECT Roles.Role, Candidates.CandName
FROM Candidates RIGHT JOIN Roles ON Candidates.Qualification = Roles.Requirement
WHERE ((Not (Candidates.CandName) Is Null))
GROUP BY Roles.Role, Candidates.CandName
PIVOT Roles.RequirementGroup;

Query2
SELECT AllCandRoles.Role, AllCandRoles.CandName, Candidates.Qualification, AllCandRoles.[1] AS [Primary], IIf([Role]="Accountant",0,[2]) AS Secondary, IIf([Role]<>"Manager",0,[3]) AS Tertiary
FROM Candidates INNER JOIN AllCandRoles ON Candidates.CandName = AllCandRoles.CandName
WHERE (((AllCandRoles.[1])>=1) AND (Not (IIf([Role]="Accountant",0,[2])) Is Null) AND (Not (IIf([Role]<>"Manager",0,[3])) Is Null));

[sagas1]

Thanks! I'll give that a try and let you know how it goes. Yes, my actual data is set is much larger but I'm hoping I will be able to scale up the solution.

Will I need to spell out the individual roles in the select statement Query2? Is there a way that could be dynamic without me knowing the various roles?

[June7]

Revised Query2:

SELECT AllCandRoles.Role, AllCandRoles.CandName, Candidates.Qualification, AllCandRoles.[1] AS [Primary], IIf([MaxGroup]=2,[2],0) AS Secondary, IIf([MaxGroup]=3,[3],0) AS Tertiary, Q.MaxGroup
FROM (SELECT Roles.Role, Max(Roles.RequirementGroup) AS MaxGroup FROM Roles GROUP BY Roles.Role) AS Q
INNER JOIN (Candidates INNER JOIN AllCandRoles ON Candidates.CandName = AllCandRoles.CandName) ON Q.Role = AllCandRoles.Role
WHERE ((Not (AllCandRoles.[1]) Is Null) AND (Not (IIf([MaxGroup]=2,[2],0)) Is Null) AND (Not (IIf([MaxGroup]=3,[3],0)) Is Null));