?
Solved

Secondary Indicator/Field

Posted on 2006-11-16
10
Medium Priority
?
528 Views
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.
0
Comment
Question by:wizardofoz
[X]
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
10 Comments
 
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:

 tblSkillLevel
--------------------------
lngSkillLevelID [PK, Autonumber)
strSkillLevel

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).
0
 
LVL 61

Expert Comment

by:mbizup
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.
0
 

Author Comment

by:wizardofoz
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.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 3

Accepted Solution

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

Ken
0
 
LVL 58

Expert Comment

by:harfang
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" )

Cheers!
(°v°)
0
 
LVL 85
ID: 17955617
KenSpencer's suggestion would be the way to go, in my opinion ... but perhaps I'm missing something ...
0
 

Author Comment

by:wizardofoz
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?
0
 
LVL 9

Expert Comment

by:Volibrawl
ID: 17959767
Wizard..

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;
0
 
LVL 9

Expert Comment

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

Author Comment

by:wizardofoz
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.)

Wiz
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

777 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