Combo Search Form

[Filmnoir]

I've looked at various posts and having difficulty preparing a search form. The database purpose is to record the names and skills and levels of employees.

I want a form with two combo boxes with the following values on the Search Skills Form:

Combo Box one : Skill_Header (Table)
Combo Box two : Skill_Level (Table)

When the combination of Skill_Header & Skill_Level is picked a sub form shows from this query:



Query: Skills_All_Search

and it should show the ConsultantFN and ConsultantLN whos Skill_Header & Skill_Level match. It's worth noting each Skill_Header value, is its own field in the Skills_All_Search query, with the Skill_level recorded as a value.

Preferably, once I have achieved this, I want to be able to query three sets at the same time:

Combo Box one : Skill_Header (Table)
Combo Box two : Skill_Level (Table)

Combo Box three: Skill_Header (Table)
Combo Box four : Skill_Level (Table)

Combo Box five: Skill_Header (Table)
Combo Box six : Skill_Level (Table)

and it to pull out those consultants with the three skills and correct level.

Could anyone point me in the right direction? I've tried a number of options from posts on the forums to no avail.

[jzwp11]

The main problem is that your Skills_All table is not normalized, in fact it looks like a spreadsheet. This site has an overview of normalization. That is not the appropriate way to handle data in a relational database.

Since a consultant can have many skills that describes a one-to-many relationship. Further, a skill can be associated with many consultants, so another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (consultants and skills in your case) you have a many-to-many relationship. A many-to-many relationship is handled with a junction table like this one:

tblConsultantSkills
-pkConsultantSkillID primary key (pk), autonumber
-ConsultantID foreign key to Consultants table
-fkSkillID foreign key to Skill_header table (the field you called ID)
-fkLevelID foreign key to Skill_Level table (the field you called ID)

I would strongly recommend renaming the ID fields in the Skill_header and Skill_level tables to another name that is more informative and unique.

I have attached a modified version of your database with the appropriate structure as I have described. I did not transfer all of the data in your Skills_All table to tblConsultantSkills. I only did the first 3 skills, but I did include the append queries I used as an example. You only need to run each append query once. Once you have transerred all of the data, you can delete the Skills_All table and all of the append queries.

I assumed that a consultant with a particular skill only has 1 level associated with it at any particular time. If you want to track a consultant's level for a skill over time, that would require a different table structure.

[Filmnoir]

Thanks, I'll have a look through and take it from there. It's orignally came from an excel sheet.

Once I've normalised the table, why do I need more unique ID fields in the skill_header and skill_level? Also how would I go about with the search function?

In terms of particular skills, only one level is associated per skill per consultant and no need for historic recording.

[jzwp11]

why do I need more unique ID fields in the skill_header and skill_level?

There is only 1 unique ID field in each of those tables; I was just suggesting that you rename them rather than just calling them ID. Take a look at the database I posted and you will see what I did.

Also how would I go about with the search function?

One step at a time. Let's make sure you have everything normalized and that you understand normalization.

Then let's make sure you migrate your data from your old structure to the new structure.

Then we'll worry about forms and searches.

[Filmnoir]

Okay all updated into the new structure.

Do I need to replicate the fuctionality now from the old to the new database?

[jzwp11]

If that is all the data you intend to have in your database then you can proceed with your data entry forms, although some of your original forms will have to be redesigned with the new structure.

Now regarding your search form. You said that you wanted to specify 3 skills with associated levels on which to search Did you really mean that the consultant had to have the skills, and the levels had to be at least (greater than or equal to) the skill level you specified via the combo box. That would be a little different then just finding the consultants with the 3 skills and the exact levels you specify via the combo box.

To do the greater than or equal to scenario, you would need a field in the tblLevel that ranks each level. I assume B stands for Begginer, I for intermediate and E for expert. I'm not sure how Y and N fit (assuming just yes or no) into your rating system. If I were assigning ranks, I might set B as a 10, I as a 20 and E as a 30 just in case you want to add other levels in the future that fall between your current ranks (advanced beginner=15 etc.). You can then use this field in your query to find those consultants with the skill but whose level is >= some rank.

In terms of an approach, you would need a query that pulls those consultant records that have any of the 3 skills/levels specified. Then you would create a new query based on the first query that counts the number of matches for each consultant. For example, consultant 1 might meet only 1 skill/level, consultant 5 might meet 2. You would need to determine which consultant had a count of 3 (meets all 3 specified skills/levels).

[Filmnoir]

If that is all the data you intend to have in your database then you can proceed with your data entry forms, although some of your original forms will have to be redesigned with the new structure.

Yes, I think I can do that, but I've run into a snag how do I add a complete new entry and new consultant? Before my form was based on one table, as this is now over four, how do I base a form over four?

Now regarding your search form. You said that you wanted to specify 3 skills with associated levels on which to search Did you really mean that the consultant had to have the skills, and the levels had to be at least (greater than or equal to) the skill level you specified via the combo box. That would be a little different then just finding the consultants with the 3 skills and the exact levels you specify via the combo box.

Yes that would be what I was after....

To do the greater than or equal to scenario, you would need a field in the tblLevel that ranks each level. I assume B stands for Begginer, I for intermediate and E for expert. I'm not sure how Y and N fit (assuming just yes or no) into your rating system. If I were assigning ranks, I might set B as a 10, I as a 20 and E as a 30 just in case you want to add other levels in the future that fall between your current ranks (advanced beginner=15 etc.). You can then use this field in your query to find those consultants with the skill but whose level is >= some rank.

Yes Beginner, Intermediate & Expert. I understand where your going, as a 'points system' then use this to query by looking for points above the value. (Ignore Y or N thats a skill you either have or not, not a level.)

In terms of an approach, you would need a query that pulls those consultant records that have any of the 3 skills/levels specified.

Not sure how to go about this.....

Then you would create a new query based on the first query that counts the number of matches for each consultant. For example, consultant 1 might meet only 1 skill/level, consultant 5 might meet 2. You would need to determine which consultant had a count of 3 (meets all 3 specified skills/levels).

Or this??

[jzwp11]

Yes, I think I can do that, but I've run into a snag how do I add a complete new entry and new consultant? Before my form was based on one table, as this is now over four, how do I base a form over four?

You would typically use a main form/subform design. The main form is typically based on the table that makes up the one side of the one-to-many relationship (in your case tblConsultants) while the subform is based on the table that makes up the many side of the relationship (in your case tblConsultantSkills). You would use two combo boxes in the subform, one based on tblSkills and the other based on tblLevel.

I'll let you give that a shot before moving on to the queries necessary for the search form since those will be more involved.

[Filmnoir]

You would use two combo boxes in the subform, one based on tblSkills and the other based on tblLevel.

But how would I get those two into the subform if its based on the tblConsultantSkills? I either get the numbers from tblConsultantSkills or if I try to change to bind them to tblSkills or tblLevel I get #name errors??

[jzwp11]

What I typically do is to create the main form. I Access automatically places a subform on the main form, I delete it and create my own. I usually do the subform in datasheet view (but not always). I use Access' form wizard to create the base for and then I would get rid of the controls that I intend to use combo boxes. I then use the combo box wizard to set up each one. You have to include the primary key value of the skills table as well as the text field. As you step through the wizard it will ask you if you want to bind the key field to a field in the underlying form's record source. In the case of the skill, you would bind it to the fkSkillID field in the consultantskill table. You would follow the same process for the level. In the attached database, I altered the row source for the level combo box so it did not include the yes and no records. I did this by saying select only those records where the rank field was null.

You can adjust what is shown in the combo box, by adjusting the column widths property. Usually the key field value is hidden, to do that set its width to zero. The first non-zero width field is the one you will see when you migrate away from the combo box.

BTW, this site has some good tutorials that might help you out.

[Filmnoir]

Thanks, a little confused of how the rating is now appearing in the level combo box next to the level?

[jzwp11]

I include all three fields in the combo box's row source. If you do not want your users to see it, change the column widths property to 0

The column widths property of the level combo box looks like this: 0";1";1"


This says the the first field (the ID field) will not be shown (zero value). The width of the other two fields is greater than zero so the user will see both fields when the click on the combo's dropdown button. To hide the rank field, just change the second 1 to zero. Like this

0";1";0"

Even though the user cannot see the values for either the ID or the rank (once set to zero), you can still reference those values elsewhere in the form if you need them. That may come in handy later on.

BTW, do you know how to get to the combo box's properties?

[Filmnoir]

Ok, I think I got the hang of it, I'm changing it in the Sub form's properties and it seems to have removed it from the combo list. Once this is in place how best do I set up my search forms on Consultant Name, Skills & Skill level?

[jzwp11]

Just to be clear, the subform (actually any form) has its own properties that you can adjust. The combo box (as well as any control on the form) has its own set of properties.

[Filmnoir]

Thanks, heres what I have so far.