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? ( is what im trying to do called a moving average? )