Update Query for multiple scanarios

[Archie1]

Hi I have a form (helped by you guys) which has the fields Risk Probability(RP) and Risk Impact (RI)(both are from TblRisks ) -
My next field is Risk Evaluation (RE)
I have an update query which updates RE depending the results of both RP & RI
Eg If "likely" is selected from RP and "Moderate" is selected from RI then it updates RE to "Low"
This is my SQL
UPDATE TBLRISKS SET TBLRISKS.RE = "low"
WHERE (((TBLRISKS.RP)="LIKELY") AND ((TBLRISKS.RI)="MOD")) OR (((TBLRISKS.RP)="UNLIKELY") AND ((TBLRISKS.RI)="MIN"))

I also want to have it update RE with a different outcome if other data is selected from RP & RI
Such as if "Very Likely" is selected from RP and "Severe" is selected from RI then I want it to update RE to "Very High"

Do I do this in the same query? If so, how?

Thanks

 

[MarkK]

I would develop a numeric scheme for this. Doing it in text seems like a headache. Also, then you can always calculate your RE, rather than save it as text.

 

[plog]

You should have a risk evaluation table and not an RE field in TBLRISKS. You don't store the RE with each record, but instead calculate it when you need it.

That RiskEvaluation table should look like this:

RiskEvaluations
RP, RI, RE
LIKELY, MOD, low
UNLIKELY, MIN, low
Very Likely, Severe, Very High
...

you would store all the permutations in that table, then just link to it when you need to determine the RE of an individual record.

Lastly, fix your caps lock key. It seems to indiscriminately work when you type SQL. If everything is in caps, nothing is in caps.

 

[Archie1]

Thanks for the suggestions. I will have a re-think

 

[Tieval]

Standard risk assessment in ISO90001 has RP and RI marked out of ten and multiplied together to give a RE as a percentage.