>>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
>
>If your table looks something like:
>
>ItemNumber OnHand Price
>101 3 5.00
>101 100 3.00
>102 21 7.50
>102 30 10.00
>102 20 9.00
>
>You could try:
> Select ItemNumber, AVG(OnHand*Price) AvgPrice from MyInventory ;
> group by ItemNumber

Thanks Marc,
Let me explain my problem

Item Qty Buy or Issue Price Avg Price
101 100 Buy 5 5
101 10 Issue 5
101 1000 Buy 3 At this point, 90 items left in stock @ $5. Value = $450, 1000 items at $3 value = $3000.
Total value = 3450. Avg price of issue is 3450/1090 = 3.165

As this goes on, the average price keeps on changing. If after 2 months, somebody realizes that the operator had wrongly typed an extra ZERO and the qty was 100 and price $3, this would change the price all the way down the line.

Any suggestions?