Matching Criteria from one list to another, where the criteria varies

[Noah]

I have been struggling to find a solution to the following.

I have a list of installers, who possess a variety of qualifications. The installers are in one table, and the qualifications I am tracking are in another.


Currently there are 12 different qualifications, and each installers can have between 0 and 12 to their name.

Separately I have a list of clients, who when having work done for them require a minimum level of qualification per installer.
This is set up through a client table, a type of work table, and qualification table. ( Same table as above so that both sides of the data refer to the same lists.

eg: Client A, has Roofing Work done. In order to work on his roofs, each installer must "x" out of 12 qualifications listed in the qualifications table.

I am getting stuck on obtaining a list of installers whose qualifications specifically meet or exceed that required by the client.

The qualifications are specific, in that an installer could have 11 out of the 12 qualifications, however if he does not possess one that is specifically required, he cannot go forward to do the work.

The installer to Qualifications table is 1:many.
I do not want to limit the list of qualifications as these may change or increase over time.

Any guidance forthcoming would be much appreciated.

[orange]

Tables and relationships

As I understand your post there are several installers, and there are a number of qualifications.
This is a Many to many.
To resolve I would use a junction table (InstallerPossessesQualification) with fields

InstallerId FK to Installer
QualificationId FK to Qualification
(and possibly a LevelOfQualification)

Installer---->InStallerPossessesQualification<----Qualification

Good luck

[Noah]

To make the current clearer.

You are correct in that there are many installers.

The tables involved are:

Installer: Installer_Qualifications Qualifications
Inst ID IQ_ID Qual ID
Name Inst ID Qual Name
Qual ID

3 tables to achieve the many to many relationship.

Client side:

Client: Job Job_reqs Qualifications
Client ID Job ID JR_ID Qual ID
Client name etc Client ID Job_ID Qual Name
Job Name etc Qual ID

The Qualification table is the same as the first scenario.

If roofing as Job requires "tree surgery" and "tight rope walking" as the requirements.

Installer Bob, who has "tree surgery", "tight rope walking", "Pilot" and "Bounty Hunter" would come back as a match

Installer Dave, who has "tree surgery", "Pilot" and "Bounty Hunter" would NOT come back as a match as he is missing "tight rope walking"

Installer Jim, who has "Pilot" and "Bounty Hunter" would not come back as a match as he has none of the required quals

Hope that makes it clearer.

[Noah]

Installer: Inst ID
Installer qualifications: IQ_ID, INst ID, Qual ID
Qualifications: Qual ID

Just as
Client: Client ID, name
Job: Job ID, ClientID,
Job requires JR_ID, Job ID, Qual ID
Qualifications: Qual ID

[orange]

I have not included your client table.
As I see it the client/user would fill in a form that basically identifies the Qualifications sought.

You would query the InStallerPossessesQualification table trying to match on qualificationID
with the qualificationIDs being sought.
For found records you were identify the Installer with those qualifications.
If these sought values were mandatory, you would AND those qualifications.
If you wanted to know how many qualifications match, none being mandatory, then you count the number of matches and group by Installer.

Update:

I see June has offered to look at your database. I suggest you take her up on this kind offer.
Good luck.

[Noah]

Thanks for the feedback.

I have followed Orange's suggestions by matching the installer with qualifications to the role requirements, and establishing a count of the number of qualifications per installer that matched.

This data was then matched by role and count of qualifications required. Eg: role has 2 qualifications, and that is matched to installers who had 2 matching qualifications.

The process has worked.

Thanks

[orange]

Glad you have it resolved.