Combo Search Form

[jzwp11]

Looks OK. I did modify your Search form. I replaced your two name textboxes with 1 combo boxes. (I used a query to concatenate the first and last names). After you select a name from the combo box and click the search button, the frmConsultants will open to that selected consultant. I also adjusted the clear search button accordingly and added code to the "Back to start" button.



It's getting late for me, so I'll sign off for today.

[Filmnoir]

Thanks, sorry been real busy over xmas!

Getting there with the D/B, added my extra bits.

I've implemented a search facilty on three skills by level that will populate into the report, but having difficulty understanding how like the name search it will work?

[jzwp11]

I assume that you are referring to your form frmSearchSkills. The question is do you want to find all consultants that have all 3 skills at the levels chosen? In other words:

WHERE (skill=comboskill1 and level=combolevel1) AND (skill=comboskill2 and level=combolevel2) AND (skill=comboskill3 and level=combolevel3)

Or all three with the level at least at what was chosen

WHERE (skill=comboskill1 and level>=combolevel1) AND (skill=comboskill2 and level>=combolevel2) AND (skill=comboskill3 and level>=combolevel3)

Does the user have to select something in each of the three pairs of combo boxes?

Will you ever need to find a consultant who has at least one of the three skills (replacing AND with OR)

WHERE (skill=comboskill1 and level>=combolevel1) OR (skill=comboskill2 and level>=combolevel2) OR (skill=comboskill3 and level>=combolevel3)

[Filmnoir]

WHERE (skill=comboskill1 and level>=combolevel1) OR (skill=comboskill2 and level>=combolevel2) OR (skill=comboskill3 and level>=combolevel3)

At high level, it will be identify consultants who have one or more of the skills below at the level and above.

It may be only one skill is selected, two or even three.

The idea I think is then for the report to show each consultant, the skills identified and level in the report.

[jzwp11]

At high level, it will be identify consultants who have one or more of the skills below at the level and above.

If that is the case we have to restrict the levels to those that have a rank. In other words, we cannot include the Y/N levels.

What is basically necessary is to create the filter that will be applied when the report is opened. This will require some code to accomplish.

I have added the necessary code to the attached database.

[Filmnoir]

Ahh ok I see how that works thanks. From this, I presume the right way to go is to add a secondary linked table to hold the Y/N Skills separate?

[jzwp11]

No, having a separate skills table would violate normalization rules.

Since you have no rank assigned for the yes or no levels, you cannot use the >= in the filter. The code I provided will work if you assign a rank, but I will leave it up to you where in the ranking a yes or no level would fall.

Do you handle those with skills evaluated as yes or no differently then those you have officially ranked?

[Filmnoir]

Ah I think I get it. If I assign the Yes or No a rank, say 40 & 50 then apply the extra code it should find them.

In terms of handling, the only difference is you either have that skill "qualification" or not, where the others have a skill level, B, I or E.

[Filmnoir]

Ah I think I get it. If I assign the Yes or No a rank, say 40 & 50 then apply the extra code it should find them.

In terms of handling, the only difference is you either have that skill "qualification" or not, where the others have a skill level, B, I or E.

Actually assigned the rank of 40 & 50 to Y and N, didn't need any code change and it seems to find them. Technically no one will pick N, as they will only look for Y.

[jzwp11]

I would think that a "no" level would have a very low rank even below B, so less than 10. As to a "yes" ranking, I'll leave that to you, but I would think that if someone has not been evaluated as to B, I, or E that that would carry less weight than someone who has been evaluated (just my opinion)

[Filmnoir]

Ahh no sorry I see what you mean.

The B, I , E are only skills gained through experience and hold no formal qualification. The Y & N Skills are a formal qulaification you atain.

[jzwp11]

OK, thanks for clarifying.