Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal


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

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.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

604 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