Table design help needed 1

[cbase]

I have to design a databse that will allow multiple search criteria as below.

Name
Region
Skill_1
Skill_2
Etc
Now for the hard part, of the 300 or so names, 20 regions and dozen skill types, I want to return as an answer from a query the top X percent closest to the client request.
To eplain further, I may have 2 names with the right region and all skill sets and 2 with the right skills but not in the selected region.

The end result will allow the database user to find the most suitably qualified available person even though all skills may not be available (closest match to ideal)

Thanks in advance, Don

 

[JonFer]

Have Name and Region in one table (People) with an ID for the person.

Have ID and SkillID be in a second table (PeopleSkills) so it will have a 1 to many relation with the first. Each skill is on a separate record.

When you set up the criteria, they need to enter the skills into another table (SkillsReq), one SkillID per row. The region can be entered on a form or prompted at runtime.

Now for the search query. I don't have data to test this but something like this should work:

Select ID, Name, Region,
Iif(People.Region=DesiredRegion,1,0) as RegionMet,
(Select Count(*) from SkillsReq) as DesiredSkillCount,
(Select Count(*) from SkillsReq as sr Inner Join PeopleSkills as ps ON sr.SkillID=ps.SkillID where ps.ID=People.ID) as MatchedSkillCount
From People

You can sort the results by RegionMet and MatchedSkills to see the best candidates. You might want to add a "Required/Optional" flag on the SkillReq table. Then you could have MatchedSkillReqd and MatchedSkillOptional counts in the search query by adding an additional criteria.