Tables

[jonben]

I have created a database with four fields: Paper size, paper type, paper sides and paper color. I need to create a calculation that figures the cost for each type of printing. For example, if paper size is 8.5" x 11", paper type is plain, paper sides is one and paper color is black, I need the cost to be .10 cents.

But I need the cost to change on the fly based on each field value.

Do I need to create a monster IF/THEN calculation or is there a better way?

Thanks for your help.

[jasonwood]

First you need to know how each of these variables affects the price (can you lay that out for us?). If that can be laid out on an item by item basis, it would be easier, otherwise you may have to figure out every combination and use "if" or "case" like you said.

[jonben]

As it turns out, my boss wants to be able to sort jobs on each of the fields. So I will need to figure out a monster If or Case calc with each variable.

However, it seems the case function would work better, but I am not familiar enough with it to get it to work correctly. Here is an example of what I need.

If the paper field is white paper, the color field is black and white, the side field is single side, and the paper size field is 8.5 x 11, the cost is 0.06. If the paper size changes to 8.5 x 14, the cost is 0.07. The rest of the field values remain the same.

Any ideas?

Jon

[jasonwood]

Case(Test, Result, Test, Result, Test, Result... you keep going like this, in pairs until the last item which is a default result if none of the above were met.

So it might start something like this:

Case(paper="white" & color="bw" & side="single" & size="8.5 x 11", .06, paper="white" & color="bw" & side="single" & size="8.5 x 14", .07, ...)

If there were an actual formla for calculating price rather than a table, you could avoid this massive lists of tests! (eg: base price is .06 plus certain amounts for specific options)

[djgogi]

Don't hard code things that are very probable to change in time.

Instead create an lookup table for such purpose with appropriate fields (I mean paper, colors, slide part ect. and price) with unique key for every possible combination of parameters (make it calculated based on respective ids).

Than in your master file simply look up the price field.

In this way you'll be able to add/modify parameters without having to modify the monster CASE needed otherway.

Dj

[jonben]

I guess I still don't get it.

Don't I still need to create a calculation for each possible combination? In other words, how do I calculate the price?

BTW, thanks for all your help, everyone.

Jon