Running Sum problem (1 Viewer)

Fozi

Registered User.
Local time
Today, 22:08
Joined
Feb 28, 2006
Messages
137
Folks

Following up my many posts on this (thanks to all) is there any way of limiting the Running Sum to a max limit - in my case 864? So that upon exceeding 864 it is set at 864. Thereafter reducing accordingly if subsequent weeks are in deficit.

See Mick example below. 1160 should be 864 and and his suesequent entry (-130) should return 734.

ItemID--Name--Week-------Fleximins--RunningSum--FlexiMax
54------Mick--02/03/2009-: 420----------420-------420
55------Mick--09/03/2009-: 360----------780-------780
60------Mick--16/03/2009-: 380---------1160-------864
70------Jane--02/03/2009-: 270----------270-------270
75------Jane--09/03/2009-: 360----------630-------630
80------Jane--16/03/2009-: 320----------950-------864
85------Jack--02/03/2009-: 270----------270-------270
90------Jack--09/03/2009-: 480----------750-------750
95------Jack--16/03/2009-: 230----------980-------864
99------Mick--23/03/2009-: -130---------1030-------864

I'm self taught and only know what I've done before. Help from some expert would be great.

Thanks
Fozi
 

khawar

AWF VIP
Local time
Tomorrow, 02:08
Joined
Oct 28, 2006
Messages
870
You can do this using vba this cannot be done directly in query
 

khawar

AWF VIP
Local time
Tomorrow, 02:08
Joined
Oct 28, 2006
Messages
870
I have attached a solution for you
It updates the running total in the table

you can modify it according to your requirements

Hope this solves your problem
 

Attachments

  • Running Sum with a Max Limit.zip
    19.3 KB · Views: 123

honda882000

Registered User.
Local time
Today, 18:08
Joined
Apr 16, 2009
Messages
62
I am not 100% clear on the second part of the question, but as far as updating any values greater than 864, have you tried using an update query?

For example:
"UPDATE table_name SET RunningSum = FlexiMax WHERE RunningSum > FlexiMax;"

And from what I understand on the second part (deficit):
"UPDATE table_name SET RunningSum = (RunningSum +Fleximins) WHERE Fleximins < 0;" *This would subtract since Flexmins is a negative amount.

Would this work for you? Like I said, I'm not 100% clear on the second part, but I hope this will give some idea.
 

Fozi

Registered User.
Local time
Today, 22:08
Joined
Feb 28, 2006
Messages
137
I have attached a solution for you
It updates the running total in the table

you can modify it according to your requirements

Hope this solves your problem

Karwar

This look great. Unfortunately I'm at a loss to understand how i integrate it within my application. My users enter their weekly times, day by day. From here I have a few queries which calculate the Fleximins and Fleximax.

The times are entered onto a subform within a parent form.

Thanks again for your assistance

Frank
 
Last edited:

khawar

AWF VIP
Local time
Tomorrow, 02:08
Joined
Oct 28, 2006
Messages
870
You can save the results on which you want to apply this running sum in a temporary table and perform this task on that data
 

Fozi

Registered User.
Local time
Today, 22:08
Joined
Feb 28, 2006
Messages
137
Hi Kharwar

I'm almost there thanks to your invaluable assistance!!!

One more thing I'd like your help with. Currently I've got the routine calculating the correct totals but at present after changing the times per day, I've got to exit the form, open the UpdateRunningSum form, click the button and then go back into the FRM_Staff Activity and Flexi Entries before the changes appear onscreen.

What I'd like is to integrate the Running Sum button into the above form so upon users entering their time they can click on this button and their new balance will appear.

Attached my efforts to date.

Thanks again for all your assistance.

Frank
 

Attachments

  • Running Sum with a Max Limit.zip
    290.8 KB · Views: 124

khawar

AWF VIP
Local time
Tomorrow, 02:08
Joined
Oct 28, 2006
Messages
870
You can use the code on the "On Close" event of your form through which you change or enter new times
My form was just a sample to show you how to do it


you can use code any where directly as below

Code:
CurrentDb.Execute "UPDATE Table1Sorted SET Table1Sorted.FlexiMax = UpdateRunSum([name],[fleximins],864)
 

Users who are viewing this thread

Top Bottom