SQL Formula to Sum Itself

[Scorpio11]

I am busy building an access stock system to calculate inventory. Now in the query in question, I have 2 Option to choose from: Purchases and Sales - to Calculate the purchases, it is easy, i use the below formula

Cost: IIf([Type]="Purchase",[Amount])

My Problem comes in when i sell something. To calculate the cost of the item sold, i can pretty much use the above formula and then calculate what the cost of the item is.

The Sale Formula i am using for the sale is:

-IIf([Type]="sale",(SELECT Sum(Q1.[Amount])


FROM qryRunSum2 AS Q1
WHERE Q1.StockCode=[qryRunSum2].[StockCode] And Q1.[TDate]<=[qryRunSum2].[TDate]))

The problem with this is, the formula only works the first time for the item in question, evey subssequent time for the same item, the cost calculated is off, - depending on what it was sold for, but the cost being calculated is the second time is not correct as i have an excel sheet to compare it to.

My question is: can i get the SQL formula get sum itself (from the previous record back), ie: the COST formula. That way, subsequent sales will take the correct Cost of Sale into account and therefore calculate future cost of sales correctly.

[Scorpio11]

I have attached a sample database that will hopefully illistrate my problem.

in qryRunSum2 - the column QTY shows a value of 0.00 as the end of all the records, the Column called COST, should show exactly the same number because no items left means no cost left - but i cannot get it to be that way.