Query to use previous row's data ?

[amrut]

I am designing database for an inventory. I have tables holding details for items Purchased and Issued. All the items purchased and issued are based on unique "Item Code". The items are issued to various equipments and the cost of the issued item is booked for that particular equipment. I am finding problems to calculate the weighted average using query. I can calculate through VBA using recordset but it takes more time to calculate. I think a stored query will perform faster. Am I right ?



An example is mentioned below :
Purchase Details :

Purchase date	Item code	Qty	Unit Price
June 1	111	5	15
June 8	111	3	20
June 12	111	6	25

Issue details :

Date	Op.Bal	QtyIssued	Wtd.Avg.(per unit)
June 5	5	2	15
June 10	3	3	[(3*15)*(3*20)/(3+3)]=17.5
June 15	3	2	[(3*17.5)+(6*25)/(3+6)]=22.5

The weighted average(unit cost) is calculated as -
[(Old Qty*Old Weighted Avg)+(New Qty*New Price)] / [Old Qty + New Qty]
It is basically total inventory cost of that item divided by number of items in stock on issue date. Hence, the weighted average will change after very new purchase.

Can this be achieved through a query ? I am stuck in the last column above. Any pointers/samples are appreciated.

[orange]

see http://allenbrowne.com/subquery-01.html