Issue with Calculation fields (1 Viewer)

Hi all,

Im putting together an Access risk assessment and I'm having problems figuring a way to calculate on the table. At the moment I have created the main Risk assessment table as well as three others; an injury; probability and; exposure table. Each one has a short text field for the name and then a number which can be used to calculate a full risk. In the main risk assessment table I have created lists so people can choose the appropriate name of each linked to each of the other tables and im hoping to have an extra field which will average the three values.

So my main issue is trying to figure out how to use a list to pick a piece of text but then in the calculation refer to the number in another field ie if i have a have a field of "daily" with a number of "3" in the list i would pick "daily" but then i want the calculation field to use the number "3" and then average it with two others in the same way. Is this possible at all or am I trying too hard?

Thanks alot!

Hi. Welcome to the forum. I think it's possible if you store the number and simply display the text. But, it's actually not the way we would recommend you do it anyway. You'd be better off doing the calculations in a query or form rather than in the table.

2 part answer


1-

I'm having problems figuring a way to calculate on the table

Click to expand...

Answer is don't store calculations. Rarely is it necessary and most of the time is ill advised.

2 - you'd need a numerical value associated with the text value. If being picked from a combo, the bound column would be number and the visible one "daily".


Back to #1 - you'd store the values and do the math on them in forms, reports or queries.
E.g. "full risk" control = Risk x Frequency x Impact, or
3 x 2 x 4 = 24 and choose the full risk assessment value that corresponds to 24 in your table. Note: 24 is not just a calculation but is associated with a risk factor text value in your table that corresponds to the form calculation.