Forum Discussion
Reset Running Total if another field value of a previous record has a positive value
Hi there,
I am running a query which has a Running Total field as below;
RunningTotal: (SELECT SUM(Total_On_Hand)
FROM Daily_Inventory_Report
WHERE T1.Shelf_Life_Date >= Shelf_Life_Date
AND T1.SKU_ID = SKU_ID)
I want to add a condition so that this running total stops and resets when the value of the previous 'EORisk' field is >=0. See screenshot. The above expression works for all rows except the highlighted row. 'RunningTotal' sums the amount of inventory on hand for a given batch. The 'EORisk' field subtracts 'Forecast_Usage' from 'RunningTotal' to give us our E&O Risk. If this calculation is less than zero all runs fine. But if it is >=0 as per circled example (340), then I want the next running total to reset, i.e. circled example (5610) becomes 3870, which in turn would calculate -330 in 'EORisk' for that highlighted row.
I found a subquery which uses MAX to retrieve the value of a field from a previous record but not sure how this would fit into my running total query. Any help of this would be greatly appreciated.
Thanks!
- Any chance of getting a sample accdb to work with. I, for one, often find it difficult to work with abstracted elements like the field shown and screenshots. It's quicker to have real data to work with. Sample data is fine if it reflects the structure and contents of the real data.
