Assign Numeric Value to Options in a Combo Group

[RogueJD]

//Edit: I didn't want to clutter and post a new thread. Uploaded current version of my DB as of 2012.12.22)

Hey all. Novice here. (I've had several relational database classes, but -online-...)


Here's the gist of it. I have a database that keeps track of Soldier performance / training. (Active duty Army.)

On the main form "SM_Form" (Service Member Form), there are several sub-forms tabbed. Some have fitness test scores, some have rifle / pistol marksmanship scores, training dates, etc.

I feel as if I normalized the data in the tables properly, and have defined the relationships properly. (Feedback appreciated, but that's not the main intent of my post)

My intent:
So, I need to create an "Order of Merit" list. This would be a list that essentially ranks each Soldier based on their performance. To do that, each Soldier would require a numeric score associated to them.

Execution:
- PT Scores are assigned OML score on a 1:1 ratio. (200 PT = 200 OML points) (Easy enough to implement. No help needed here.
- Weapon Scores: 3 Tiers; Expert, Sharpshooter, Marksman. Each tier would have a numeric OML Point value associated to it. (The three options are selectable combo group value list options in the "Weapons" subform. - This is what I need help on.)

So, how do I assign the text value of, say "Expert" on the weapons subform to numeric 70, display it on the "OML_Subform", and store the value on a table?

DB included. Please, please look at it so all of the above makes sense. (Open Form "SM_Form")

 

[Isskint]

by combo group i guess you mean Option Group? (set of radio buttons, tick boxes, toggles etc in a frame). If so then they already have a numeric value which the Frame uses to return the value of which item is selected. You could use that value either in a literal expression (eg OptionGroup * factor) or to DLOOKUP() in a lookup table so the factors can be changed without going into the coding.

 

[RogueJD]

Thanks, Isskint.

By combo group, I meant value list. I read somewhere that it's best to use a value list on a form, rather than have the option group on a table.

Here's my current dilemma; I'm unable to view data from other subforms on the "OML_Subform". All it shows is "#Name?" in form view. I'm fairly certain all relationships, master and child, are correct.

Could someone look at this DB and tell me what I need to do to the "OML_Subform" to make it display "Primary_Qual" from the "Weapons" table?

(Clarification info: )
- Main Form = "SM_Form"
- Several tabbed subforms on "SM_Form" (e.g.: "Weapons_Subform", "OML_Subform"
- "OML_Subform" needs to display-only data from "Weapons" table. (This field should not allow for data entry, only viewing)
- "Primary_Qual" from "Weapons" table has a value list. One of four options can be selected, null, Expert, Sharpshooter, Marksman.
- Subform "OML_Subform" will make a calculation. If "Primary_Qual" from "Weapons" table = "Expert", then that's say 100 points for them. That score should be stored on the "OML" Table (Data fields not yet defined in the OML table in the above-linked DB).
- "OML_Subform" will add up all OML points, and store on "OML" table.

 

[Isskint]

You have no relationships set up in your database. But that is not the problem. You have the Control Source of the textbox set to Weapons!Primary_Qual but this is not an available source of data for the table you have set as the forms Record Source. Change it to =DLookUp("[Primary_Qual]","[Weapons]","[SM_ID]=" & [Forms]![SM_Form]![ID]). Similarly change the Secondary weapon texybox source to =DLookUp("[Secondary_Qual]","[Weapons]","[SM_ID]=" & [Forms]![SM_Form]![ID]). The only "problem" here is that if you change weapon type on the weapons tab it will not reflect on the OML tab until you refresh - something you can set up in code for when the focus moves to the OML tab.

If you set up relatonships on your DB and created an all encompassing query to use as the record source for all those forms, then your current =Weapons!Primary_Qual would be closer to working except replace Weapons with the query name.

 

[RogueJD]

I forgot to re-assign the relationships after I changed some of the tables' data types.

Thanks for the info! That solution taught me a lot!

 

[RogueJD]

Seeking clarification on how to assign a numeric value to weapon scores.

Please see DB for details. I'm referring to "SM_Form", "OML" Tab (which loads the "OML_Subform").

If you score Expert, you get say 150 points. Sharpshooter = 130, Marksman = 90. (These values are defined on the "Weapons_Subform")

How do I do that?

I'd like to learn the method, because I'll need to apply that technique to other values that are established on other subforms.

Also - The "Adverse_Action" field will be input on the "OML_Subform". It's a yes / no. I'd like for that to carry a negative value. (If you have adverse action, you shouldn't be on an OML). If "Adv_Action" = yes, then "Adv_Action_Score" = -1000

I tried the method described in reply #2, but received an #Error.

//Edit - Solved.)

=IIf(DLookUp("[Primary_Qual]","[Weapons]","[SM_ID]=" & [Forms]![SM_Form]![ID])="Expert",150,IIf(DLookUp("[Primary_Qual]","[Weapons]","[SM_ID]=" & [Forms]![SM_Form]![ID])="Sharpshooter",130,IIf(DLookUp("[Primary_Qual]","[Weapons]","[SM_ID]=" & [Forms]![SM_Form]![ID])="Marksman",90,IIf(DLookUp("[Primary_Qual]","[Weapons]","[SM_ID]=" & [Forms]![SM_Form]![ID])="Unqualified",0))))