Running Total for every record

[leighturner]

My running total based on box count is not producing a running total for each record?

RunTot: DSum("BoxCount","FRA 799 Value Card","BoxCount")

For example box count data:
126
7
13
8
10
7
13
8
7

Running total is showing 199 for every record
What I am trying to achieve is the following:

126
133
146
154
164
171
184
192
199

Thanks for your help in advance.

 

[vbaInet]

Do you have a number field that can be sorted? Without this you can't get the Running Sum unless you use a report.

So the data should look like this:

ID        Boxes
1           126
2           7
3           13

From that you can get a running sum.

 

[leighturner]

Thanks for the quick response vbaInet.

Could I not create a new running total field based on current box count data?

ID Boxes RunTot
1 126 126
2 7 133
3 13 146

 

[RainLover]

vbaINET is correct.

Perhaps the attached may help.

 

[vbaInet]

Also, the count requires an ID with your set of data. It will fail if the records are not DISTINCT and needs to be sorted.

Looks like RainLover has something for you.

 

[leighturner]

Thank you so much RainLover...

That worked an absolute treat, I cannot thank you enough.

I have another question, sorry.

Is it possible to reset the balance running total once a value is reached?

 

[RainLover]

It is possible but how I am not 100% sure how. It would require an IIF Statement to test the previous Value.

I will try to have a look at this tomorrow if not answered by someone else in the mean time.

 

[vbaInet]

It can't be done easily unless via some manipulation with a Dictionary object.

You would need to use a report if you want to achieve this easily.

 

[RainLover]

Thank you so much RainLover...

That worked an absolute treat, I cannot thank you enough.

I have another question, sorry.

Is it possible to reset the balance running total once a value is reached?

This is very difficult in a query.

Could you please explain in much more details how when where and why the reset is applied.

I need sample data or something more helpfull.

 

[leighturner]

Thank you for your replies and help so far.

Basically now I have my running total working, I would like a further query to monitor the running total, when the running total reaches >=18 reset the RT by taking the BoxCount query that the RT is using, this query or another query would then identify "New Pallet" when RT has reached >-18.

I can provide my DB so far if this would help?

Cheers.

 

[RainLover]

Thanks for the info.

I will see if I can do this. It may take sometime.

 

[leighturner]

Thank you very much RainLover.

Would it help if I sent you the data?

 

[khawar]

Check my post here it will give you some idea

 

[leighturner]

Thanks for the info.

I will see if I can do this. It may take sometime.

Hi RainLover,

I dont want to be a pain but have you had chance to look into my query at all? - I am still at a total loss.

 

[RainLover]

I don't know what happened but I posted a reply ages ago. I must have done some thing wrong.

Bacially the answer is that due to the complexeties of your requirements, I, along with others, believe that you will need to use VBA to achieve what you want.

Suggest you put your thoughts together and start a new post that deals with this problem only and is not confused by my SQL solution.

 

[vbaInet]

I think I have already mentioned the only way it can be done in post #8. You will use a Dictionary or Collection object. But it needs to be manipulated and it requires every record to have a unique id. As already mentioned too, it will be much easier if done on a report.

 

[leighturner]

I think I have already mentioned the only way it can be done in post #8. You will use a Dictionary or Collection object. But it needs to be manipulated and it requires every record to have a unique id. As already mentioned too, it will be much easier if done on a report.

Designing a new report appears to be the only option, would it be possible explain the process of my requirement if I were to complete this inside a report?

 

[vbaInet]

Declare the count variable in the declarations section of your form:

Option Compare Database
Option Explicit

Private counter as Long

Put the code to run the counter in the Format or Print event of the Detail section:

counter = counter + 1

If counter > [COLOR=Red]10[/COLOR] then
     counter = 1
End If

[COLOR=Red]txtCounter[/COLOR].Value = counter

Where 10 is the max number you would like the number to be resetted and txtCounter is the name of the textbox to display the count.

 

[leighturner]

Declare the count variable in the declarations section of your form:

Option Compare Database
Option Explicit
 
Private counter as Long

Put the code to run the counter in the Format or Print event of the Detail section:

counter = counter + 1
 
If counter > [COLOR=red]10[/COLOR] then
     counter = 1
End If
 
[COLOR=red]txtCounter[/COLOR].Value = counter

Where 10 is the max number you would like the number to be resetted and txtCounter is the name of the textbox to display the count.

Thank you for your response.

I understand what you have explained with the code but do not understand the declarations sections??

 

[vbaInet]

... but do not understand the declarations sections??

At the top of your report's module, you will see the first line, i.e. Option Compare Database, add those other two lines just below it.