Tables

This topic is 7825 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Share on other sites

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.

Share on other sites

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

Share on other sites

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)

Share on other sites

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

Share on other sites

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

Share on other sites

This topic is 7825 days old. Please don't post here. Open a new topic instead.

Create an account

Register a new account