freeze price change (1 Viewer)

[steve111]

hi ,

I have a form ( stocklist) that has stone sixes in it for example
description = "2000*100*500 stone red sand" and part number is "srs215" and let say that parts cost 20£
when I select the part from the stocklist the part , description and cost populates into my orders form
so now I have a record of that order of what the part cost to but £ 20" and what I sold it for " 25£"

2 months later the cost of part srs215 has increase to 22£ there all my orders previously show " 22£ "

is it possible that the first order can freeze the 20£ record
but the new orders show "25£"

thanks
steve

 

[pbaldy]

I would have a field for price in the orders table and store the price at the time of the sale.

 

[steve111]

yes , I can have
but if it is 20 £ today and 1 want to say sell for 20% profit £24
but next year it goes up 20% for the part so I put the cost in the stocklist form to 24£ and sell it for say 26£
if I look back at order "1" it will say I sold it for 24£ but the profit was zero as the cost. has gone up

I if possible was trying to keep a history of the price for the part at the time I sold it and the sell price at that time

steve

 

[pbaldy]

You can also store the cost with the order, though it's probably less common.

 

[jdraw]

If you rely on (Product) Price in the Product table to calculate Order Total Cost/Price, you will risk changing the Price on all historic Orders. A better technique is to include the "AgreedToPrice" in the OrderDetail record. Usually this is the unit Price that was Agreedto for this Product on this Order for this Customer. The advantage of this approach is that you can change the Price of the Product in the Product table without affecting historic Orders. Also, it allows you to have clearance, sale items, loyalty program etc without having to have multiple prices in your product table.

You can record ProductPrice in the Product table, if you record the ProdEffectiveStart date and the ProdEffectiveEnd date. This will allow you to use a ProductPrice throughout the effective duration period, then as the Price changes you value the ProdEffectiveEnd and create a new ProdEffectiveStart
date with the new Price (which will be the current price until the price changes). However, I strongly recommend you record the SellingPrice with the quantity of Product in the OrderDetails. This represents the selling price of this Product to this Customer in this Sale. This will allow you to have Clearance sales, loyalty programs, specials etc.

Good luck.

 

[HiTechCoach]

There are two common ways to handle the costing.

1) store the cost along with the price in the order. Not the best form of normalization

Note: it was used back when databases were a lot slower and the volume of sales was really high. It was more out of necessity that for a good design.


2) Following the rules of data normalization you would have a cost sub table. This also follows good accounting principles.

Example:
Record 1
item: "srs215"
Start date: 01/01/2014
End Date: 21/12/2014
Cost: 20£

Record 2
item: "srs215"
Start date: 01/01/2015
End Date: 21/12/2015
Cost: 25£

To get the cost you would use the Order's sale date and look up the record where the sale date falls withing the date range pf the record for that item.

This has many benefits that I feel out way the extra effort to make it work.

 

[steve111]

hi
thank you all for helping me , I have created a table called product cost as suggest in the last post and copied all the fields described , I have form "quote" and a subform called "order details subform" these are attached to a navigation form.
in the quote form there is a date from when the quote was raised , and in the Subform there is the part field called " productno " this part come from a dropdown box which looks at table called stocklist

at present the cost comes from the stocklist table which is wrong . how do I now tell the form " order details to look up the part I have selected then get the cost from the product cost table with the date on the used from the quote

i do not know how to do the lookuo

my subform is from a query , I can put the quote date in the query , and the product cost table in also and link the parts together from the stocklist and product cost , but is it possible to query on the date from the quote against the start and finish date in the product cost table

thanks again steve

 

[steve111]

this is the query that creates my subform
you can see the stocklist cost which I will change
I have now got the product cost in against the part
how do I tell the query to only bring the records that are to the nearest to date1 and start date /enddate of the product cost

Query1 Date1 OrderID startdate enddate product cost.Cost ProductNo 21/08/2015 1 01/01/2015 31/12/2015 50 12*14*29RS 21/08/2015 1 01/01/2015 31/12/2015 560 12*10*14RS 21/08/2015 1 01/01/2015 31/12/2015 6 17*10*14RS 21/08/2015 1 01/01/2015 31/12/2015 70 12*15*14RS 21/08/2015 1 01/01/2015 31/12/2015 70 12*15*14RS 21/08/2015 1 01/01/2015 31/12/2015 65 10*15*14RS 21/08/2015 1 01/01/2015 31/12/2015 54 8^15^14RS 21/08/2015 1 01/01/2014 31/12/2014 560 12*10*14RS 21/08/2015 1 01/01/2014 30/06/2014 5 12*14*29RS 21/08/2015 1 01/06/2015 31/12/2015 5 12*14*29RS

I should only see 7 records for this to be correct
the first eight ninth should not be there

thank steve

 

[HiTechCoach]

This has been continued in a new post here:

added table now cannot enter data

I have posted a solution in in the post above.