Multiple Prices for same product

[Guest]

Hello World! I am working on a dbase for sales order entry (like template)
and need to have different price levels or prices (eg. 3 levels) for the same
product. Each company has an entire pricing level assigned to them (either
level 1,2 or 3). How do I set up the diffent pricing levels? I have a table
for customers, orders, order details and parts.

 

[Amy Blankenship]

Are the price levels pretty much random, or is there a mathematical
relationship... For example, would level 2 be level 1 * 1.5 and level 3 be
level 1 * 2, etc.?

 

[Duane Hookom]

I would count on there being 4 or more levels in the future. Consider a
normalized approach where the PriceLevel is a field that stores a value like
1, 2, or 3.

tblPricingLevels
===================
PriceLevelID PK
ProductID
PriceLevel
UnitPrice

You may need more complexity if your levels vary over time and you need to
keep a history of pricing levels.

 

[Guest]

Hi Amy,

The prices are random, unfortunately

Are the price levels pretty much random, or is there a mathematical
relationship... For example, would level 2 be level 1 * 1.5 and level 3 be
level 1 * 2, etc.?

 

[Amy Blankenship]

I would probably do it something like this:

Company
=========
CompanyID
CompanyDesc
CompanyPriceLevel
Companyetc.

Product
========
ProductID
ProductDesc
ProductEtc.


We'll assume that product prices can change over time and that you'll want
to retain an accurate view of what the prices were.

PriceLevelProductDates
===========
PriceLevelDateID
PriceLevel
PriceLevelBeginDate
PriceLevelEndDate
ProductID
Price

If all prices change at once, you would want to have a different structure,
so you didn't have to enter begin/end dates for each and every product. You
also could assume that when a new price level begins the old is superseded.
That makes the queries harder, but prevents dead times with no price and
overlaps.

HTH;

Amy

 

[Guest]

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!

 

[Guest]

Thanks Duane - foresight for growth is always a good idea! How do I get the
value in orderdetails form to pull the right value from pricing levels? Do
not need historical pricing data!

 

[Amy Blankenship]

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

 

[Guest]

Do not need historical pricing data!

You have mentioned that you do not need historical pricing data. However,
you are keeping track of orders and order details. If you change the price
on an item, that new price will show up on all of your old orders, and you
will no longer have an accurate record of orders. Would this be a problem?

 

[Duane Hookom]

You create a query of your Company table and the Products table and the
Pricing table.
Join
tblProducts.ProductID -> tblPricingLevels.ProductID
and
tblCompany.PriceLevel -> tblPricingLevels.PriceLevel

 

[Guest]

Again, Thanks but these seems a little over my head. I have loaded the dbase
on my site http://www.davidlawrencemarketing.com/valvoline/valvoline.htm.

Is there any way you can take a peek? I'll make a donation to your favorite
charity?

 

[Amy Blankenship]

I just downloaded the latest version of your db. I didn't have time to go
through it in depth, but I noticed several things.

1) There's something odd about the relationship between WorkOrder and
OrderParts. When you double-click on it, the tables and fields are not
filled in.
2) There is no relationship between PricingLevels and Customers to enable
you to determine the pricing level for a customer.

HTH;

Amy