Hi there... I'm having touble working out the best way to tackle this problem....

 

We have a stock system where by products can come in at varying prices... sometimes even we can be buying 10 of something and get the 10th item for free.

In order to show a fair price for out commision based sales force, it is prefferred to show an averaged stock price on the sales order.. so instead of 9 items at £200 and one at £0 (because it was free) we would show each product at (200/10) ie an averaged price.

 

So I have a products table and a purchase orders table and a stock table...

 

I am trying to work out what the averaged stock price would be from the products table using information from the purchase orders table and stock table but im not having much luck...i'm not even sure if its possible to have a dynamically calculated field on the fly working this averaged stock price out!

 

Obviously the purchase orders table is aware of what stock has been ordered and delivered into stock etc... and once the stock has been sent the record is deleted from the stock table.

 

 

Any ideas?

How are you planning on working out the average of the same stock but on different purchase orders?

 

For example I buy 10 Hard Drives at £40 each but I get the 10th free so it is averaging at £36 per drive.

 

The next week the supplier calls me and says if I buy another 10 I can have them for £30 each so on this purchase order the average price of a drive is £30

 

I now have 20 Hard Drives in stock.The first batch cost an average of £36, the second batch £30.

 

When you create a sales order, unless you are somehow going to pick stock from a specific P.O. what average cost are you going to use?

 

An average cost of all hard drives in stock?

The Sum of funds spent/number of drives?