To All Members,

I have a problem. I am designing an inventory control system. They buy goods, issue them to the shop floor, make new items and sell them. I need to calculate the average price of the items being issued. This is not simple. If qty ten of an item were bought at $5 each and three issued, then issue price was $5 each. If next day the same item qty 100 bought at $3 each, average price if different. We have 7 items left at $5 so value is $35, 100 items at $3, value is $300. Total value = $335. Total items = 107. So average price = 335/107 = $3.13.
The problem is, I dont want to store this price with the Item in the product file because the purchase voucher and the issue vouchers may be ammended at any time. This will offset the price. Now at the end of the month they want to evaluate the stock, can I write a simple SQL statement and get the avg price of each item?

Regards and thanks in advance,

Abdul Ahad