Re: Multiple Prices for same product


"ashlanddave" <ashlanddave@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E818BBDF-D329-4A77-87CC-BE59E855905F@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Multiple Prices for same product
    ... details subform. ... customers get a break that varies by prodcut. ... I have added a new field for each customer in customers table: a pricing ... We'll assume that product prices can change over time and that you'll want ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Informix business grew by double digits in 2006 (Q1+Q2)
    ... but I can't think of any of our customers ... GET THERE FASTER with Informix. ... I do not see PVU pricing as a Godsend for IDS. ... server either on a stand-alone box or as a partition within a server. ...
    (comp.databases.informix)
  • Re: SFWA Folds its ePiracy Committee - For Now
    ... "Value pricing" is an attempt by ... Hows do they compel customers to buy the product, ... effect that potential customers will pirate e-books if they don't like ...
    (rec.arts.sf.composition)
  • Re: SFWA Folds its ePiracy Committee - For Now
    ... "Value pricing" is an attempt by ... Hows do they compel customers to buy the product, ... effect that potential customers will pirate e-books if they don't like ...
    (rec.arts.sf.composition)
  • Re: Giantantispywareupdater
    ... > Customers determine pricing by withholding their money. ... Thanks for confirming that MS doesn't care what their paying customers think ... Alias ...
    (microsoft.public.windowsxp.general)