Lookup value from matrix

[AbsoluteBeginner]

Hi there,

Apologies in advance for my lack of Access terminology, but I was wondering if somebody could help with a problem I have.

I have a form with 3 fields. The first field is titled "Cost" and the second titled "Certainty", based on the attached matrix.
What I would like to see in the third field is a automatically-populated value from the matrix when the two previous fields are populated,

i.e. If "Cost" = 2 and "Certainty" = 4, 56 is the result.

I've tried building an expression along the lines of:

IIf([Cost]=0 And [Certainty]=1, "0",IIf([Cost]=0 And [Certainty]=2, "0",IIf([Cost]=0 And [Certainty]=3, "0",IIf([Cost]=0 And [Certainty]=4, "0",IIf([Cost]=0 And [Certainty]=5, "0"........etc. but am told this is too complex.

Is there some kind of Lookup function I could use to make the job a lot simpler? Suggestions on how to go about this will be gratefully received!

Many thanks,

AB

 

[pbaldy]

If you had a table with those values, it would be relatively simple. However, the layout Access would handle best would be a table with 3 fields, Cost Certainty and ResultValue. Your matrix would look like the attached.

 

[The_Doc_Man]

The third field is a computed value (because a lookup counts as "computed"). You do not ever want to do this.

First, a query is your best friend for computed values. You can do anything with a query as a recordsource that you could have done with a table as your recordsource - and more.

Second, in a global module, you can build a public function using VBA that could be made to recover data from a matrix of some sort. The function would have to be single-valued. I.e. regardless of how it is called, it must resolve to a mathematical scalar value. Not a CONSTANT value, but a SCALAR value.

How you do the lookup in that function is up to you and can get as complex as you need it to be. But it won't work in table. Only in a query.

 

[pbaldy]

Doc, I'm not clear on what you mean by "You do not ever want to do this". If you mean save that looked-up value to a table, I'd disagree. I would generally agree, but there are times when it is appropriate. An example would be saving the price of a product in a sales application. I look up the price from a products table, but I may also save it in the line items table, because the price in the products table will change over time. I need to save the price applicable to this particular sale. A table with prices and effective dates is an alternative, but at some level of price volatility becomes impractical.

I don't know if this is a static or dynamic matrix, but I can certainly visualize a scenario where the looked-up value needed to be stored. If those values never change, then I wouldn't store it. I know you to be very intelligent and I respect your views, so I'm open to the possibility that I'm missing something. Wouldn't be the first time.

 

[AbsoluteBeginner]

Thanks very much for the helpful information, folks.

I'll go back to the drawing board and see how I get on.

Best wishes,

AB

 

[The_Doc_Man]

pbaldy, you are correct if the matrix content is dynamic based on dates.

Store anything that you need to compute something after-the-fact if one of the components of the computation is time-based. But you STILL can't express that in a table 'cause tables don't store functions.

He would STILL need to have an UPDATE query to do the actual work for him.