Creating a Text Box to Display a Running Total

[Art_Of_War]

Hi all,

I'm new here, the (nick)name's Art_Of_War. Glad to be here, it seems like a good place to post Access-related questions and get a specific answer that solves the problem.

I've been designing a database for a client who wants his Accounts form to display a running total; that is, a text box which displays the sum of previous "Balance" entries plus the current entry but no more than that. What I've got so far is a field that sums my Credit and Debit fields for the whole form, but this is not much good to us because it displays the same value in every Running Total field.

Is there a way to create a proper, functional Running Total using the Expression Builder, or should I start playing around with VBA arrays etc.?

My client says it's standard accounting practice to have such a field in there (although it's not actually on his Accounts slips).


See pictures for more info, I hope I've explained everything so that it makes sense.




Figure 1: Accounts form


Figure 2: Accounts slip

Hope you guys can help me work this one out, it's one of the few final touches I need to add to the DB before he starts using it (and pays me for it).

Regards,
- Art_Of_War.


P.S. This thread might belong in a different section, I can't seem to move it after having posted it.

[pbaldy]

On a report you can use the running sum property of a textbox. You can also do it in a query:

https://support.microsoft.com/en-us/...crosoft-access

[Art_Of_War]

Thanks a lot for your response!

To follow up with another question, HOW would I implement this in the context of a report? I.e. what syntax would I use for the code/expression behind the Running Total text box?

[pbaldy]

Did you look at the property? No code or expression, just set the property appropriately. Perhaps you can post a sample db?

[Art_Of_War]

http://databasedesignsolutions.com.au/MusicDBTest.zip

[Art_Of_War]

You're a moderator, @pbaldy--would you mind moving this thread into the "Access" forum? Thanks

[pbaldy]

Is this what you're after?

[Art_Of_War]

Yes! Exactly that! You're a legend, mate... how did you get that result? (And how much do I have to pay you to disclose the secret? )

[pbaldy]

I did exactly what I suggested; bind the textbox to the balance field and set the running sum property.

[Minty]

This appears to be a very similar question to this one https://www.access-programmers.co.uk...d.php?t=297161
Cross-posted methinks ?

[Art_Of_War]

And the code for the running sum is...?

[Minty]

There isn't any.
As Paul's explained, it's a property setting in the reports control.

[Art_Of_War]

OH Snap, there it is! Should I do it "Over Group" or "Over All"?

[Minty]

That depends on if you have grouped the reporting by any field. For instance if your report grouped by customer then you would want it "over group"
If not "Over All" should do it, but run some sanity checks on the data to make sure it's correctly doing what you want it to.

[Art_Of_War]

Ok thanks, I'm marking this thread as "Solved."

And may the cretins at access-programmers.co.uk rot in hell! :P