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.