Select different rates

[Richio]

I have a simple "orders" database. A main order form with a subform.

the subform lists the product with a combo box and then enters the unit price from the same table automatically.

My problem is that a product can have 9 prices dependant on price band. (Unfortunately there is no common formula for each product, so a discounting structure cannot be used)

The easy solution for me is that I enter 9 different product codes for each product in my product table, which would work, but due to the number of products this would make my table very long and would not be suited to the way updates are made.

What I would like to do is extend my product table so each product could have 9 prices

Ie

Product 1 / Unit Price 1 / Unit Price 2 / Unit Price 3 etc

It is worth noting that on each order all of the products must be in the same price band as this hopefully would make it easier.

Problem is, I am stuck in even thinking about it, ideally I would like a combo box on the order to select the price band, then every time the product is ordered the unit price on the subform picks up either unit price 1 / unit price 2 etc dependant on what was selected. Even better still, if the unit price band is changed it will recalculate the unit price for all the products.

1. Is the possible
2. Any pointers in the right direction to get me going would be appreciated

thanks

 

[Mile-O]

That's a bad way to go...don't go there!

Create a table:

tblUnitPrices
PriceID
Price

then in your product table add one field called PriceID, change its caption to Price and, in the relationships, link the two PriceID fields in a one-to-many relationship.

 

[neileg]

I think Mile is right, but not completely right. My table would look like this:

tblUnitPrices
ProductID
Band
Price

ProductID is the primary key from the product table Band identifies your price bands 1 to 9, and price is the price. To idntify the correct price you would need to know the ProductID and the relevant price band.

 

[Mile-O]

lol, Monday mornings I can hardly see the monitor let alone what's on it - I read a bit and thought "no, don't go Price 1, Price 2, Price3, etc." but skimmed the rest.

Yep, neil, more thorough as usual

 

[Richio]

Neil

Are you saying have a brand new table, so my exising table would have Product1 for example and then a further table would include nine price bands for this? why not just list the nine products in the first table.

I am confused

However.......since looking further into it I have got it to work (sort of, need to look at the refresh / requery)) using the table with the Price1 / Price 2 columns etc using a combo box for the ProductID and a new priceband field using

If [Priceband] = 1 Then Me![UnitPrice] = Me![ProductID].Column(4)
If [Priceband] = 2 Then Me![UnitPrice] = Me![ProductID].Column(5)

in the after update of my productID.

You are saying "don't even go there" with this table. Will it get to complicated if I extend the above to 9 if functions? or can I even put 9 if functions in?

Thanks again

 

[neileg]

What both Mile and I are referring to is called database normalisation. I won't go into the ins and outs, but you might care to investigate this concept.

I'll give you a couple of reasons why I would go this route rather than the daisy chain (my term) approach.
- The obvious one is the one you suggest. If you want to have more than 9 bands, you have to rewrite the database.
- Secondly, you wont be able to keep a history of prices. In your design, new prices will simply overwrite the old values. If you have a separate table you can attach dates to the prices and use the most recent only.

I could go on.

The advatages aren't always obvious, and usually show themselves when you need to alter things, and you wish you'd followed the normlised route from the beginning!

 

[Mile-O]

Also, investigate:

1st Normal Form
2nd Normal Form
3rd normal Form.

 

[Richio]

Thanks.....

I see exactly where you are coming from....it is all to easy to jump in and get something that works only to discover later why it was a bad idea.

I will see how I get on with this method

R