Running Sum with a Beginning balance 1

[PROXI]

Hello everyone,

I am trying to do a running total in my report. I have no problem with the running sum in the detail section of the report. The problem that I am running into is that I want to have a starting point. I have a textbox in the header that has a value in it that is pulled from another table. I want that to be my starting point for the first entry and then do the running sum from that point. This is what I am trying to do:

Beginning balance: 100

1 50 150
2 -10 140
3 20 160
4 10 170
5 -10 160


I am not sure how to make this happen. Any help that can be offered would be appreciated.

PROX


Thanks,

PROXI

 

[dhookom]

Have you considered creating a union query from the "another table" and the report's current record source?


Duane
Hook'D on Access
MS Access MVP

 

[PROXI]

I always hate it when someone solves a problem and doesn't share the way they did it to help anyone else that may have that same problem.

I figured a way of doing it that is much simpler than any other form of the running sum I could find. The best part is that it doesn't involve making special queries or tons of VBA programming.

I have one table that contains the beginning balances of my accounts. I have one table that has a sum of all of the activity for each day since I am working with over 1 million records in my database. (This condenses the time of the report each time to almost nothing. as opposed to spanning all of the records - I have a query that runs each time the user imports files to append those sums to the table)

In the header of the report I have 2 text boxes. The first textbox (Text19) has this code:

=DSum("[Amount]","QryBal")
(This gives me the amount of all of the transactions up to the day before the user put in on the form)

the second (Text17) has this code:

=DLookUp("BegBal","TblBal","[Account]= 15931131 and [BU]=20101")+IIf([Text19] Is Null,0,[Text19])
(This adds the beginning balance of that account along with the amount above)

Text 17 gives me my beginning balance for my account. From here I have put a text box in the detail section of the report with a Control Source of "=1" and set the Running Sum of the textbox to Over All. This is used to give each row a row number. I set this field to be invisible.

I created a textbox name RunTotal. This textbox has the running sum set to Over All. The Control Source is set to:

=IIf([RowNum]=1,[Text17]+[Amount],[Amount])

All of this combines to make a running total that is very efficient and easy.

Thanks,

PROXI