Reset Running Total if another field value of a previous record has a positive value

[eoinew]

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!

 

[theDBguy]

Hi. Welcome to AWF!

It might be easier to come up with a working suggestion if you could post a sample db with test data.

 

[arnelgp]

you need a function to do that?
i created Query1 which will sort your data by Shelf_Life_Date.
i created a function in Module1 that calls Query1 and compute the runningTotal.

the final query is Query2.

 

[eoinew]

Thanks theDBguy arnelgp! Really impressive arnelgp!

I implemented your solution arnelgp, but the 'EORisk' is not a field in the table, its a query field, so ran into a bug. I've attached some sample data that will give you a better understanding. There's also a join with a 'Forecasts' table which holds monthly sales forecasts. Batches/Lots are to be grouped by SKU. I'm using a table called 'Daily_Inventory_Report' for this query but I have also include a table called 'Transactions', as this would really be a better table to work from. The Daily_Inventory_Report table has Lot Codes repeating because of different warehouses, but its the total per Batch/Lot that really matters - warehouse is irrelevant. So maybe a sum of each Lot Code from the Transactions table, and then a running total as outlined above would be better.

Thank you for your help.

 

[arnelgp]

see query1 if this is what you want.
note that EORisk column on Query1 is based on the original EORisk from EO_Risk Query.

 

[eoinew]

That's it!

However, I updated the EORisk column to calculate from the new query using the new RunningTotal column (EORisk: [RunningTotal]-[Forecast_Usage] from Query1). The original EORisk was an incorrect calc due to using the old RunningTotal column.

See updated file attached.

Thank you arnelgp!!!

 

[eoinew]

Hey arnelgp,

I tried amending so that the query runs off a sum of the Total_On_Hand query instead of the Daily_Inventory_Report table, as I only want one row per Lot_Code in the final query result. I think the ID field is causing a bug in Module1 but I'm out of my depth as to figure out a solution. See the attached amended sample db. Any ideas?

Thanks.

 

[arnelgp]

hello!
i created a temporary table (zTable).
the structure is same as the output query.
then i created a datasheet form for zTable (dsEO_Risk).
on the Open event of the form, there is a computation
going on before the records are displayed.

the zTable will hold the final output you need.
in case you don't want to use the dsEO_Risk form,
and want to view the result directly from zTable.
you need, first, to run the Sub Module1.RefreshZTable
before you open zTable.

 

[eoinew]

In the meantime I worked on the attached - nearly there but it is generating an error due to no 'EORisk' column in the EO_RIsk query I think.

 

[eoinew]

arnelgp - Thats exactly it! Amazing.

There is another condition/calculation I need to add to this. When the 'EORisk' column of the previous record is >=0 the RunningTotal needs to reset (which is done, thank you), but also the 'Number_of_Months' column needs to decrease by the value in the previous record, i.e. 20-6=14. I'd be interested to see where this fits into your VBA.

 

[arnelgp]

here is the change.