Help with vlookup/hlookups

[dfsabrown]

Hello

I need some help with an expression for a field. I have a “RiskReg” table with a “probability” field (input options are L/ML/MH/H), an “impact” field (input options are also L/ML/MH/H) and “RiskRate” field. I need access to calculate the “RiskRate” field for me depending on the results of the “probability” & “impact” fields. I have the following “RiskRAG” table in access to lookup the right scores:

RiskRAG
field Prob L Prob ML Prob MH Prob H
impact H 4 8 12 16
impact MH 3 6 9 12
impact ML 2 4 6 8
impact L 1 2 3 4

What do I put in the “RiskRate” field to be able to look up this table? Im thing a vlookup & hlookup on the “RiskRAG” table but don’t know how

Many thanks

 

[dfsabrown]

sorry table not clear, but its basically

impact H, Prob L then Risk Rag = 4
impact H, Prob ML then Risk Rag = 8
impact H, Prob MH then Risk Rag = 12
impact H, Prob H then Risk Rag = 16

impact MH, Prob L then Risk Rag = 3
impact MH, Prob ML then Risk Rag = 6
impact MH, Prob MH then Risk Rag = 9
impact MH, Prob H then Risk Rag = 12

impact ML, Prob L then Risk Rag = 2
impact ML, Prob ML then Risk Rag = 4
impact ML, Prob MH then Risk Rag = 6
impact ML, Prob H then Risk Rag = 8

impact L, Prob L then Risk Rag = 1
impact L, Prob ML then Risk Rag = 2
impact L, Prob MH then Risk Rag = 3
impact L, Prob H then Risk Rag = 4

 

[pbaldy]

The table should look like post 2, not post 1. That way it's a simple lookup with 2 criteria, or a join in queries.

 

[dfsabrown]

so a table like this:

IMPACT/PROB/RESULT
H L 4
H ML 8
H MH 12
H H 16
MH L 3
MH ML 6
MH MH 9
MH H 12

etc, etc

what lookup formular would i use then, i need to check 2 fields

thanks!

 

[pbaldy]

You can use a DLookup() with 2 criteria:

http://www.mvps.org/access/general/gen0018.htm

or in a query you can join that table to another on those 2 fields.

 

[dfsabrown]

im sorry i still dont get it :-(

im not sure which to use numeric or string.

the "RiskRate" field on the RiskReg table/Form needs to come from the numberic results filed in the "RiskRAG" table, but i am looking up 2 text fields "probability" & "impact" from the RiskReg table to the RiskRAG table.

Also am i looking up the filed i want filled ("RiskRate") and the 2 criterias are "Probability" and "impact"?

SO:

Dlookup("result","RiskRAG", "criteria1 ="RiskRAG"!"IMPACT"="RiskReg"!"Impact"AND criteria2="RiskRAG"!"PROB"="RiskReg"!"Probability")

is that even close????

 

[pbaldy]

Close is relative.

Depends on where you're using it. On a form, as a textbox control source it would look like:

=Dlookup("result","RiskRAG", "IMPACT='" & [Impact] & "' AND PROB='" & [Probability] & "'")

Presuming Impact and Probability were fields on the form.

 

[dfsabrown]

PERFECT!!! thanks so much!

 

[pbaldy]

Happy to help! I think you'll be happier in the long run. That design is easier to work with plus will handle new probabilities much easier than what you had.

 

[dfsabrown]

Hello - me again! i have just created a report on this form you helped me with but the value that we have calculated is not showing on the report, the field is just blank. i have had a look at the field/report properties & can't work out why the value is not showing?

 

[pbaldy]

I can't see the properties from here . Can you attach the db?

 

[dfsabrown]

can't im afraid its confidential. but which properties do you need to see, the RAG field in the form, or report properties??

 

[pbaldy]

Well, the same formula you have above should work on a report, presuming the fields in the criteria are on the report. I'd be willing to bet the tables could be joined in the report's source query, which would be most efficient.