ashlanddave said:
Thanks Amy, I have a table setup now.......
I need to figure out how to modify / insert the unit price into the order
details subform (orderdetails.unitprice). agian, the problem is that
there
are different prices for the same product based on different customers -
some
customers get a break that varies by prodcut.
I have added a new field for each customer in customers table: a pricing
level e.g. customers.pricing (=a,b,c). Each product has a productID,
partnumber in table: Products. I have also created a pricinglevels table
with the following fields:
[pricinglevelID, part number, pricing, unitprice] with pricing=a,b or c
Customer Product
Pricing
Levels
------------- ------------------
---------------------------
customerid productid
pricinglevelid
pricing productnumber
productnumber
addresses, etc productname pricing
unitprice
So I can't pull the unit price from the products table
(products.unitprice)
in the order detail subform directly as in the sample db. I need to match
up
the correct product, prcinglevel and pull that into the order detail's
unitprice field.
I do not need to keep historical or date information on pricing!
Let me ask you a question: If a customer makes an order today and tomorrow
you need to change the pricing level for future orders, and the order
doesn't get filled and the invoice printed until the day after tomorrow, how
would you ensure that the invoice showed the correct price so the client was
billed correctly? That's the sort of situation keeping historical pricing
data allows for.
Also, you should probably be relating on ProductID instead of PartNumber.
You need to create a query that joins the CustomerID to the ProductID and
UnitPrice in the PricingLevels table, as well as joining to the Products
table to retrieve the decryption of each product. Then use that query as
the datasource for your subform, and use CustomerID as the
LinkMaster/LinkChild fields.
HTH;
Amy