If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple Prices for same product
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. |
#2
|
|||
|
|||
Multiple Prices for same product
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.? "ashlanddave" wrote in message ... 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. |
#3
|
|||
|
|||
Multiple Prices for same product
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. -- Duane Hookom MS Access MVP "ashlanddave" wrote in message ... 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. |
#4
|
|||
|
|||
Multiple Prices for same product
Hi Amy,
The prices are random, unfortunately "Amy Blankenship" wrote: 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.? "ashlanddave" wrote in message ... 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. |
#5
|
|||
|
|||
Multiple Prices for same product
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 "ashlanddave" wrote in message ... Hi Amy, The prices are random, unfortunately "Amy Blankenship" wrote: 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.? "ashlanddave" wrote in message ... 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. |
#6
|
|||
|
|||
Multiple Prices for same product
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! Dave "Amy Blankenship" wrote: 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 "ashlanddave" wrote in message ... Hi Amy, The prices are random, unfortunately "Amy Blankenship" wrote: 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.? "ashlanddave" wrote in message ... 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. |
#7
|
|||
|
|||
Multiple Prices for same product
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! "Duane Hookom" wrote: 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. -- Duane Hookom MS Access MVP "ashlanddave" wrote in message ... 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. |
#8
|
|||
|
|||
Multiple Prices for same product
"ashlanddave" wrote in message ... 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 |
#9
|
|||
|
|||
Multiple Prices for same product
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? |
#10
|
|||
|
|||
Multiple Prices for same product
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 -- Duane Hookom MS Access MVP "ashlanddave" wrote in message ... 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! "Duane Hookom" wrote: 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. -- Duane Hookom MS Access MVP "ashlanddave" wrote in message ... 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. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Compare product prices from two supliers and produce report | [email protected] | Worksheet Functions | 0 | June 9th, 2006 07:16 AM |
Office 2003 Standard MultiLingual User Interface (MUI) installation | [email protected] | Setup, Installing & Configuration | 3 | May 23rd, 2006 11:59 AM |
how do i find unique avg buy prices in multiple group of buys/sell | John Robbins | Worksheet Functions | 1 | May 4th, 2006 06:44 PM |
Printing Different first page headers | Cat's Meow | Setting Up & Running Reports | 9 | January 10th, 2006 01:03 PM |
multiple copies of office which product key is correct?? | Shawn. | Setup, Installing & Configuration | 1 | October 9th, 2005 05:05 AM |