Odd question about mathmatical formulas and tables

[Xarkath]

Hello,



Before digging into this I just want to say that I fully expect that this is not possible but some of you have already amazed me so I figure that it can't hurt to ask.

We have clients that provide life benefits to their employees and the benefit amounts can be calculated in a number of ways. For example, the benefit could be 2 times annual salary rounded up to the nearest 1000 but capped at 75,000. Or 75% annual salary rounded up to the nearest 1000 capped at $50,000. I am guessing that I have a minimum of 20 different scenarios and that is just for life. Some clients have long and/or short term disability as well.

What I am curious about is if I can create a table that cross references the clients to thier formula (to clarify: the formula would be stored in a field on the table) so that it can be used as a calculator by the end users. Basically thinking that on a form they could enter the client ID and the salary then push a button and get the resulting values based on the formulas stored in the table.

I can create group specific queries or write a bunch of IIF statements but it seems like there should be a way to do this more efficiently and also in way that would allow someone more than just myself to easily add new formulas as we aquire new clients with life benefits.

Thanks in advance for any information or insight you may have on this.

[orange]

Have you worked through the scenarios to look for a pattern or some sort of grouping. In other words, do you have some written calculation/algorithm now?


You could try something along these lines.....you know the business better than the readers..

If years of service >x and <y and
salary between S1 and S2 then use option 2

If years of service <x and
Salary between S1 and S2 then use option 4


You could then determine "the amount" by selecting the option that applies.

[June7]

I won't say no but I will say it could be difficult. Access/VBA has an Eval function. This means a string of characters: "11 * 2 / 3" can be forced to be mathematically evaluated:

Eval("11 * 2 / 3") = 7.3333333

So if a field stores that string, the function call could be:

Eval([field name])

and the content of the field would be evaluated.

I don't really think that's what you are looking for. Will probably need VBA custom function. Store in the field some parameter that says which calc to use then call the custom function:

MyCalcFunction([calc type], [salary])

and branching structure in the code will lead to the correct calculation.

[orange]

I agree with June7. My option 2, option4 were intended to be algorithms or functions.

[John_G]

Just to add to what June7 and Orange have suggested -

Look up how to use the Select Case ... End Select statement - it is easy to work with in VBA, and this is a perfect use for it.

John

[Xarkath]

Hi June,
This is actually perfect. I just tried it on our worst case scenario and it works exactly as I had I hoped. And it will be much easier to train someone to enter the formulas than it would be to train them to update VBA code or such.

Much thanks for the help, it is really appreciated.