Reset Running Total if another field value of a previous record has a positive value (1 Viewer)

eoinew

New member
Local time
Today, 09:21
Joined
Nov 30, 2021
Messages
6
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!
 

Attachments

  • E&O Risk.jpg
    E&O Risk.jpg
    108.5 KB · Views: 180

theDBguy

I’m here to help
Staff member
Local time
Today, 01:21
Joined
Oct 29, 2018
Messages
18,756
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

..forever waiting... waiting for jellybean!
Local time
Today, 16:21
Joined
May 7, 2009
Messages
16,086
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.
 

Attachments

  • run_total.accdb
    512 KB · Views: 187

eoinew

New member
Local time
Today, 09:21
Joined
Nov 30, 2021
Messages
6
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.
 

Attachments

  • SC_Database_Demo.accdb
    2.9 MB · Views: 190

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:21
Joined
May 7, 2009
Messages
16,086
see query1 if this is what you want.
note that EORisk column on Query1 is based on the original EORisk from EO_Risk Query.
 

Attachments

  • SC_Database_Demo.accdb
    2.9 MB · Views: 187

eoinew

New member
Local time
Today, 09:21
Joined
Nov 30, 2021
Messages
6
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!!!
 

Attachments

  • SC_Database_Demo.accdb
    2.9 MB · Views: 120

eoinew

New member
Local time
Today, 09:21
Joined
Nov 30, 2021
Messages
6
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.
 

Attachments

  • SC_Database_Demo.zip
    172.2 KB · Views: 92

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:21
Joined
May 7, 2009
Messages
16,086
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.
 

Attachments

  • SC_Database_Demo.zip
    41.9 KB · Views: 180

eoinew

New member
Local time
Today, 09:21
Joined
Nov 30, 2021
Messages
6
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.
 

Attachments

  • SC_Database_Demo2.zip
    196 KB · Views: 181

eoinew

New member
Local time
Today, 09:21
Joined
Nov 30, 2021
Messages
6
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

..forever waiting... waiting for jellybean!
Local time
Today, 16:21
Joined
May 7, 2009
Messages
16,086
here is the change.
 

Attachments

  • SC_Database_Demo.zip
    42 KB · Views: 191

Users who are viewing this thread

Top Bottom