Secondary Indicator/Field

Posted on 2006-11-16
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 84
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.

Accepted Solution

kenspencer earned 200 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" )

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

LVL 84
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now