HELP with matching problem

[Fjordur]

Hi,
I'm stuck with a query I can't seem to be able to write, would be very
grateful for help. This is Access 2000.

I have 5 tables
- table 'persons' with identifier personID (+ some fields)
- table 'skills' with identifier skillID (+ some fields)
- table 'missions' with identifier missionID (+ some fields)
- as each person may have 0 to many skills, table Person_Skills with 2
fields personID and skillID (unique index)
- as each mission requires 1 to many skills, table Mission_Skills with 2
fields missionID and skillID (unique index)

As you may have guessed, I want to find the person(s) who can take a given
mission, which means they must have at least all the skills for the mission
(maybe more skills of course). This is not symmetrical.
I figure it all happens between the tables Person_Skills and Mission_Skills
but I've spent hours on this for nothing...

Thanks for your time

 

[David S via AccessMonster.com]

Hi Fjordur,

What you need to do here is to figure how many skills are required for each
mission, and then how many of those skills each person has. So, to start:

Mission_Skill_Count (totals the number of skills required for each mission):
SELECT Mission_Skills.missionID, Count(Mission_Skills.skillID) AS Skill_Count
FROM Mission_Skills
GROUP BY Mission_Skills.missionID;

Mission_Persons_SomeSkills (counts how many skills for each mission that each
person has):
SELECT Mission_Skills.missionID, Person_Skills.personID, Count(Person_Skills.
personID) AS Skill_Count
FROM Mission_Skills INNER JOIN Person_Skills ON Mission_Skills.skillID =
Person_Skills.skillID
GROUP BY Mission_Skills.missionID, Person_Skills.personID;

Mission_Persons_AllSkills (from thosse people woth some of the skills,
figures out how many of them have all of the skills required by comparing
each person's skill count with the skill count for the mission overall):
SELECT Mission_Skill_Count.missionID, Mission_Skill_Count.Skill_Count AS
SkillsRequired, Mission_Persons_SomeSkills.personID
FROM Mission_Skill_Count INNER JOIN Mission_Persons_SomeSkills ON
Mission_Skill_Count.missionID = Mission_Persons_SomeSkills.missionID
WHERE (((Mission_Persons_SomeSkills.Skill_Count)>=[Mission_Skill_Count].
[Skill_Count]));

 

[Fjordur]

Hi, David,

What you need to do here is to figure how many skills are required for each
mission, and then how many of those skills each person has. So, to start:
(... 3 queries)

Wow!!
I'll have to meditate on those queries you wrote. They work just fine, and
I've incorporated them in my application..
Thanks a lot, really!