Creating Calculated fields in a Table based on two other Fields in the same table

Question

I would like to create a calculated field in a table that would return a value based on values in two other fields which have a drop down to enter values. As an example,

Field 1 has a drop down from which I can select the frequency level values, the list has Frequent, Probable etc.

Field 2 has a drop down from which I can select the severity values, the list has Insignificant, marginal, catastrophic etc.

Now, if for my first entry, if I select a frequency of 'Frequent' and a Severity of 'Insignificant' I want the field 3 to display 'Undesirable'. For other values of Frequent+ any value from field 2, I want field 3 to show 'Intolerable'. Similarly there are other values for probable and so on. I have tried using the IIf function but I am not getting the right logical value.

Note: The drop down for fields 1 and 2 come from a look up from another table.

Answer 1

The expression one can use in a Calculated field is limited. If you are using IIF() functions, then do it in a query. If you are using complex IFs, do it in a code function.

Answer 2

I don't think it's wise to put these rules in code. It is better to record in a table which combinations of frequency and severity lead to a certain value of "field3".

That way you can also easily adjust or expand rules.

And because the rules are already saved, you no longer need a calculated field.

Answer 3

Thank you. I already did that as there are only 24 combinations of it and it is a reference table. But I wanted to know the right way to use the IIf condition for similar situations because at a later stage I would only be having the frequency and severity fields for a bigger table having 200-300 records and I wouldn't want to keep doing the risk assessment (Field3) manually.