I am trying to do a look up based on two fields. Here is the situation:
- my first field is called Severity and it is the primary field of a “severity definition” table, which contains 5 records
- my second field is called Probability and it is the primary field of a “probability definition” table, which also contains 5 records
- I have created a junction table, called “Risk acceptability” which is built with the two above-mentioned fields; the primary field is built as a formula (Severity&"-"&Probability). On that table is the field “Acceptability” which is the one I will try to lookup. See below
-Finally, my FMEA table will allow for any new record to pick a Severity and a Probability: I would like to automatically have the “Acceptability” appear. So I have created the same formula in that table (Severity&"-"&Probability) to lookup to the table “Risk acceptability”. Problem is: I cannot create a Lookup based on the result of the formula.
So my question is: how can I use my formula result for a lookup ?
Or is there any other way ?
Thanks for your advice