Running Totals

[jlgray0127]

I know this is has several threads and I can do this successfully using queries, but I have a situation I am trying to create in a form.


What I am doing is creating a production schedule for our manufacturing facility.
This is a blank canvas, so looking for some creative ideas!

I have a temp table for the part which shows what we need to cover the schedules each week. For the factory, we need to try to level load this demand, meaning we have orders like, Week 1, 1200 pieces, week 2, 1000 pieces, week for 2300 pieces, etc.
We would try to level load this at 1835 pieces per week....

For each record, defined by the week date, we would want to load 1835 on those 3 lines.
I would like to do a running total in the form where we are scheduling, which adds 1835 to week 1, and then 3670 would calculate for week 2 and then 5505 for week 3 and so on... we are scheduling way more than 3 weeks at a time and we may have some 0 weeks in there ect, for shared assets to run other products.

Thoughts on how I could create the running total output? Should this just be a query that recalculates each time we load a week or is it possible to write this formula to an unbound text box in the form?

I have a unique ID and all that to calculate the running total... just not 100% sure on how to tie all the logic together to do this.

[June7]

Could possibly accomplish with DSum() expression in textbox.
Provide data sample. Either build tables in post with the Advanced post editor toolbar or attach a file.

[jlgray0127]

Dlookup is where I went, but am struggling to get it to pull correctly.

=DLookUp("[RunningTotal_Sched]","[Sys_Schedule_RunningTotal]","ID=" & [Forms]![MTP_WorkBook_PeriodSchedule]![MTP Workbook Scheduler].[Form]![ID])

I also just tried "ID="& me.ID

Both result in #Name? - I am not sure what I am missing.
I want the RunningTotal_Sched Field from the query Sys_Schedule_RunningTotal Where the ID = the ID Field on the subform MTP Workbook Scheduler

[jlgray0127]

Got it!

=DLookUp("[RunningTotal_Sched]","[Sys_Schedule_RunningTotal]","ID= '" & [Forms]![MTP_WorkBook_PeriodSchedule]![MTP Workbook Scheduler].[Form]![ID] & "'")

[June7]

Why is ID field a text type?

Is this calculation located on [MTP Workbook Scheduler]? If so, just use [ID] as the reference, don't need fully qualified reference.

Strongly advise not to use spaces in object naming. Why did you for this object but not others?

You say you want a "running sum" but looks like you are just pulling a total sum. Sum() on form doesn't provide what you want?