Secondary Indicator/Field

Hi Experts
                My question is probably quite simple for you more knowledgeable experts but has me stuffed.

I am building a db for work to record the skill levels on staff members in various tasks. I have chosen to give each a numeric rating so that I can average the various teams and the entire workforce thereby giving a reference in comparison for each person to both their team and the entire company.

Management however want a visual/written indicator in for form of being either “Incompetent”, “Needs Improvement” or “Competent”.

I initially placed 3 labels on the in-put form and using code for “Case” I was able to have the appropriate label visible when each record was updated. However, if I were to return to the form and index through the records the label appropriate for the last record would remain visible for all records until I updated one of them.

What I would like to have is a form with two related fields. Field 1 would be where I can in-put the numeric value and Field 2 would then update with the appropriate written rating. Would I need a 2nd field in the underlying table?

I await your wondrous solutions.
Who is Participating?
kenspencerConnect With a Mentor Commented:
Of course, there are several ways to do this.  If you want the results table-driven, you could have a table called tblScores:

lngScore        lngSkillLevelID (to be compatible with LSMConsulting's table)
1                           1
2                           1
3                           1
4                           2
5                           2
6                           2
7                           3
8                          3
9                          3
10                        3

Or just one table:
lngScore     strSkillLevel (again, using LSMConsulting's field name)
1                Incompetent
2                Incompetent
3                Incompetent
4                Needs Improvement
5                Needs Improvement
6                Needs Improvement
7                Competent
8                Competent
9                Competent
10              Competent

And it could also be driven by just code (not recommended here, though, as you would probably need it in several places).

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Would seem to me to be easier to have a second table to hold the values, and relate the tables together, something like:

lngSkillLevelID [PK, Autonumber)

So perhaps your table would look like this:

lngSkillLevelID    strSkillLevel
1                      Incompetent
2                      Needs Improvement
3                      Competent

In your other table, you're alreadly storing the numeric score; just make sure they coincide with the values in tblSkillLevelID. Now rebuild your forms Recordsource to include the new table (in the form's design view, click in the Recordsource field, then click the Build button immeditaly beside the textbox to open the query builder). Make sure to correctly join your main table's SkillLevel field with tblSkillLevel.lngSkillLevelID. Include the strSkillLevel field from tblSkillLevel into your recordset, and then drag that field onto the form.

Note that you may have to alter the Join properties in order to make you query updateable. To do this, after joining your two tables together, highlight the line between the two tables and right click, then select Join Properties. This allows you to make a choice; typically you'd select the "Show all records from <your main table name> and only show related records from tblSkillLevel" (or something like that).
Have you considerd an Option group control?  You can use a group of radio buttons or check boxes labeled “Incompetent”, “Needs Improvement” and “Competent”.   If you bind the option group to a field in your table, the user's selection will be stored as a number, and displayed as the approptiately checked option.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

wizardofozAuthor Commented:
Perhaps I need to explain that we have 6 Teams of staff, each team being managed by a Team Leader who will grade each team memeber in the various skills areas. As the Team leader will be in-putting the rating I only want them to in-put a numerical value. (From experience I prefer to keep the interaction/in-putting as simple as possible - some of our people are techno-dynosaurs.) These values may be 1 - 3 = Incompetent, 4 - 6 = Needs Improvement and 7 - 10 = Competent. By simply registering the skill level the visual aspect will default into the 2nd field. I thought of a Combo box working off a 2nd table but couldn't work out how to show a numerical value for the pick list (which would be recorded in the underlying table for comparison and averaging later) but after selection display the alpha rating of Incomp/Needs Imp/Comp etc.
Hello wizardofoz

As you will want the same treatment on averages later on, you can use an expression that accepts non integer values. For example, given the skill is stored or calculated in txtSkill, you can use this in a text box:

    = IIf(Skill<3.5, "Incompetent", IIf(Skill<6.5, "Needs Improvement", "Competent") )

This would show "Competent" for Null values however. So you might prefer:

    = IIf(Skill<3.5, "Incompetent", IIf(Skill<6.5, "Needs Improvement", IIf(Skill<=10, "Competent", Null) ) )

... I was forgetting Switch() again. This is more readable:

    = Switch( Skill<3.5, "Incompetent", Skill<6.5, "Needs Improvement", Skill<=10, "Competent" )

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
KenSpencer's suggestion would be the way to go, in my opinion ... but perhaps I'm missing something ...
wizardofozAuthor Commented:
Kenspencer - I had a table exactly like your second vesion (1 / Incompetent; 4 / Needs Impr........etc) but could not work out how to input a numeric value in 1 field and have it automatically show the written rating in a second. I was playing with a "Iif [Field1] <4 then "Incompeent" " etc but could not get it to work or was putting the Iif expression in the wrong place - think I was using the Validation Rule property on a second (unbound) text box.

I've just had a thought (unusall !) (sarcastic). If Ii created an expression and linked it to a second table - as per kenspencer's 2nd version - then placed a sub-form with a text box bound to the written rating in the expression then this might work. I just thought there wouldd be an easier solution.

Does this point anybody to a simpler design?

I've just had a thought (unusall !) (sarcastic). If Ii created an expression and linked it to a second table - as per kenspencer's 2nd version - then placed a sub-form with a text box bound to the written rating in the expression then this might work. I just thought there wouldd be an easier solution.

Just link the 2 tables in a query.
Base your form on the query.

Then when you add or change the level on the form, it can automatically display the (table2) description value for that level.

Be sure you have set the table2 level as a Primary Key (no duplicates).

SELECT Table1.Employee, Table1.Level, table2.Description
FROM Table1 LEFT JOIN table2 ON Table1.Level = table2.tbl2level;
Is that clearer?  .. this is what LSM and Ken were trying to get to.
wizardofozAuthor Commented:
Hi Guys
         I've come up with a solution to the connundrum and as kenspencer was incidental in this solution I shall award the points to him even though the final structure was of my own design.

I had already considered a 2nd tbl listing the 10 rating levels and their associated written description but had not been able to get it to work. What I have done is in the primary table I have a field named Rating (numerical value). I have then created a qry based on the 2nd table and created a sub form based on that qry. The link fields are Rating/Rating which are both numerical so that when the rating is applied on the main form the written description appears on the sub form. As I will be able to place it together without borders etc it will simply appear as another field.

Thank you all for your efforts and ken, enjoy the points. (I'm not sure if they are like fly-buy rewards points at the store where you need to have 2156600000 to redem a packet of chewing gum.)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.