Look up based on range

[Baylah]

I am wondering if someone can help me figure out how to accomplish something.

I have a table that has only one record, it is a price list and it has 69 fields on it that represent specific pricing tiers based on Qty of Product Ordered and Number of Colors in the product.

An abbreviated version of the price table looks Like this:

Quantity

..............1C.....2C.....3C........4C.....5C.....6C

6-11.......2.95...3.50...4.00

12-35.....1.75...2.50...2.75

36-71.....1.25...1.55...1.95...2.35...2.75...3.15

I have a second table where a user inputs order information and a price is generated based on the certain properties. In this case, qty of product ordered and number of colors in the product.

What I need to happen is for a user to enter:

Quantity in one field (qty)

Number of colors in another field (NumCol)

To get a result that is the appropriate price. (i.e. using above, 7 pieces with 2 colors returns 3.50, 63 pieces with 6 colors returns 3.15.)

I have accomplished this using a case statement but then if my client ever changes their price list they need me to come out and update the DB. While that is good for recurring income I don't think it is very professional.

Does anyone have a suggestion for how I can get where I need to be?

Thanks in advance for any help.

Steve

Edited April 11, 2007 by Guest

[David Jondreau]

One record with 69 fields and a Case statement? Ugh. You need 69 records. And Windows XP.

The fields would be:

MinQuantityOrdered, MaxQuantityOrdered, NumberColors, Price.

You can then relate the Order to the Price table and have it look up the prices via the relationship.

Order::Quantity >=Price::MinQuantityOrdered

Order::Quantity<=Price::MaxQuantityOrdered

Order::NumColors = Price::NumberColors

This setup needs a little modification if the number of colors is a range too, but the principle is the same.

[Baylah]

I know the case statement is ugly, that is why I am looking for a better way. It worked...I just it can be better.

I will try and figure out what you have posted. thanks for the help and wish me look.

Steve

Edited April 11, 2007 by Guest

[Baylah]

David,

I'm doing something totally wrong...can you possibly elaborate?

thanks,

Steve

[David Jondreau]

You need a separate record for each of your price points. In your table create the fields I outlined above, create a record for each price point and fill in those fields, then delete the extra 69 fields.

Then you create a relationship between the Price table and the Order table using the three criteria above. This relationship will be doing the legwork the Case statement was doing.

Post the file and I'll show you want I mean.

[Baylah]

Post the file and I'll show you want I mean.

Thank you for your generous offer. Attached is the file I have been working with. This is still a work in progress so I have not made it pretty yet. And I have to clean some unused fields, etc.

The Full Access User Name is admin but I did not put a password on the file. However you will have to hold down shift when you open because I have the open script set up for limited access so a user cannot create records...they do not need to in this solution.

The tables/layout in question is called the Expanded Screen Calculator. You will notice there is a table in here for Expanded Stitch Calculator. That one works ecaxtly how I wnat the Expanded Screen Calculator to work.

I added the fields you suggested to the table called Order.

I am still trying what you suggested but I am not there yet.

Play around with this and I think you will understand what I am trying to do.

Thanks again for your offer to help. I owe you a beer if you tell me how I can pay up!

Steve

SuperCalcEarly4-12.zip

[David Jondreau]

Basically, you need a relationship to do what your Case statement is doing.

I made three changes:

Added relationship between Orders and Price List

Changed SCP_Base to lookup price in Price List instead of using Case.

Added sample price records.

You'll need to add the rest of the data in and change the SCP layout to suit your needs.

SuperCalcEarly4-12.fp7.zip

[Baylah]

David,

You are the best! If you're ever in the San Diego area lunch is on me!

Thanks,

Steve

I had just figured this out when I saw your attachment. Thank you for putting so much effort into this for me.

[David Jondreau]

You're welcome.