Looking for suggestions

[Gina Whipp]

Hi All,

Scenario:
tblStandardPricing - Just what it says
tblCustomerPricing - Customers that pay another rate
tblOrderDetail - Sometimes another price comes into pay because the Order is
special (presently there is no way to enter this price YET. This is the
basis of my issue.)

If a Customer has a special price I get it from tblCustomerPricing, if not
the pay tblStandardPricing. but NOW the boss wants to be able to add
pricing based on a particular Order. My question is where do I store this?
I am only storing the tolling rate however, it needs to be stored and I
hesitate to put it in the tblCustomerPricing because it is a one time price.
The next Order or the next line item could revert to tblStandardPricing or
tblCustomerPricing. Or am I thinking of this all wrong? Ideally, I would
prefer not to have to hunt three tables for 1 price and a union query I'm
thinking is not a good choice. Any ideas?

Thanks,
Gina

 

[mscertified]

You need another table tblOrderPricing, I guess this table will need to have
the order number in it.
Ideally, the order pricing would not depend on the order but on the quantity
(discount for quantity or maybe discount per $ amount) which would be able to
be calculated based on a fixed formula from the other prices and the quantity.
Maybe this is what the boss really wants.

Dorian

 

[Gina Whipp]

But then am I still left searching 3 tables for the price when it comes to
invoicing? I guess I was trying to get out of it but I can't???

 

[TedMi]

But then am I still left searching 3 tables for the price when it comes to
invoicing? I guess I was trying to get out of it but I can't???

You shouldn be able to limit your search to a single table by including the
pricing basis in the order detail table. For invoicing, the basis will tell
you which ONE of the 3 tables to search for the price, and the criteria for
the search (e.g. product only for std. pricing, customer/product for customer
pricing, order no. for special pricing).

 

[Gina Whipp]

I believe I understand what you are saying but perhaps I should explain
better what I mean:

tblStandardPricing = Tolling .06
tblCustomerPricing = Tolling .05
tblOrderPricing = Tolling .10 (new table as per suggestion)

The standard price is the price every customer should pay BUT some customers
have worked out a special price based on volume so that needs to be
stored... AND THEN some orders because of the difficultly may get and yet
another price. But the Order itself in no way indicates what type of Order
it is. Coils are received, different weights, to be applied towards orders
which have the total weight/pieces. There is no 'criteria' for determining
which Order gets what price.

I have already come to the conclusion that 3 tables holding pricing
information is the best way to go. And my logic is, if tblOrderPricing has
no price for the Order go to tblCustomerPricing and check there is nothing
there go to tblStandardPricing where there will always be a price.

Thanks for your suggestion but your way I think I would need three tables
per item: product; customer/product; order no. OR maybe I misunderstood.
Gina