>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