Use Values Selected From Combo Box in Form to Run Calculation in Query

[ZachAtaiyan]

Hello,

I have been working in MS Access for the past few weeks, but I have very little experience with queries. Here is what I am trying to do:



I have 3 fields with combo boxes on a form and each combo box has three choices: High, Medium, and Low.

I would like to establish a numerical value with each field depending on what is selected. For instance, High=5, Medium=3, Low=1.

I plan on taking the 3 fields and using the selected values in a calculation that would generate a score and I would like to store this value on a table.

My question for you guys is; is there any information you could send my way that can assist with the development of this?

Here is a picture of what I am reffering to on my form:



Thanks in advance!

[ItsMe]

Some here may prefer to use a Value List for the combo box in instances like this. My preference is to use tables and have the Combo Boxes Row Sources look to the tables. I would create a table for each of the Combo Boxes/factors, Probability, Severity, etc. Each table would have a column for the description and a column for the Primary Key.

[ZachAtaiyan]

Any idea on how I can assign the numbers based on the ranking selected?

[ItsMe]

You could use a bound control as a combo's Control Source. Another option is to use SQL and or DAO to update/append records.

[McArthurGDM]

Since you are drawing those combo box values from somewhere, I would draw them from a table and have each table set up like this:

Probability_Table
______________________________________________
[ID] (#field)..........[Rating]
5.........................High
3.........................Medium
1.........................Low

And then do the same for the Severity and Detection tables, etc...

This way, if "High" is selected, it automatically corresponds to the number 5 - etc...for each table.


Give each table a unique reference ID (the same as the value if you don't want to get confused)

Now, on your Main table where you keep the records, set up 3 (or however many you need) fields where you can create a relationship with the other tables you just created. Call the fields ProbabilityMain, SeverityMain, DetectionMain and you'll see the user input for the text values. Make sure that you have a number field in your table left blank for the time being called "SumMain" or something.

Now, create a query. In particular, create an UPDATE QUERY.

Call upon your main table and whatever other tables you created and join them.

Run the query for the Values in the main table and then also run the ID fields in the other tables you created. You'll see that if you run the query, for each record, High matches with 5, Medium with 3, etc...

Then call upon the SumMain field in your main table. Under the UPDATE TO criteria below - put

=SUM([ProbabilityTable].[ID], [SeverityTable].[ID], etc...)

It will update the SumMain field with all the values you want.



It is important to note that the update query must be ran regularly to make sure that the values in the SumMain field are indeed there. For report or whatever purposes.

[ZachAtaiyan]

I am going to try and restate my issue, because I can't seem to find anything out there on what I want to do.

I have a Field with a combo box on a form containing 3 values, "High", "Medium", and "Low". Depending on what the user selects, I would like to populate another field with an integer and use that integer in a calculation.

How can I do this? Thanks

[ZachAtaiyan]

Thank you! just saw that I recieved replies while I was typing. haha

[McArthurGDM]

Let me know if you have issues - but it should work.

[ZachAtaiyan]

While I am giving this a try, my combo boxes are now displaying 1,3,and 5 instead of Low, Medium, and High. What am I doing worng?

[McArthurGDM]

When you create the combo box, make sure it's on the wizard. You can call upon the particular field (High, Medium, Low) for each table. You can choose to turn off or on the ID field in that case.

[June7]

If you are saving the number value, maybe you need multi-column combobox. Review http://www.datapigtechnologies.com/f...combobox3.html

[ZachAtaiyan]

I don't really know how, but I got it to work. I didn't even have to use the query, just the Combobox wizard. Thanks again!