Secondary Indicator/Field

Posted on 2006-11-16
Medium Priority
Last Modified: 2010-07-27
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.
Question by:wizardofoz
  • 3
  • 2
  • 2
  • +3
LVL 85
ID: 17954746
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).
LVL 61

Expert Comment

ID: 17954755
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.

Author Comment

ID: 17954896
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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Accepted Solution

kenspencer earned 600 total points
ID: 17955265
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).

LVL 58

Expert Comment

ID: 17955295
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" )

LVL 85
ID: 17955617
KenSpencer's suggestion would be the way to go, in my opinion ... but perhaps I'm missing something ...

Author Comment

ID: 17958788
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?

Expert Comment

ID: 17959767

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;

Expert Comment

ID: 17959781
Is that clearer?  .. this is what LSM and Ken were trying to get to.

Author Comment

ID: 17963104
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.)


Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question