Forwarding balance for running sum

[Anne]

I created this query with a running sum, which is sorted by date then by
transaction number. I put this into a form for entering transactions for an
investment money market account. It works very well.

However, now that I have one years worth of data in there, I would like to
limit the data for the form as well as for reports. As soon as I limit the
data range, I loose the beginning balance.
Can anybody give me an idea on how I could create a beginning balance when I
select a date range or a starting date?

 

[PieterLinden via AccessMonster.com]

I created this query with a running sum, which is sorted by date then by
transaction number. I put this into a form for entering transactions for an
investment money market account. It works very well.

However, now that I have one years worth of data in there, I would like to
limit the data for the form as well as for reports. As soon as I limit the
data range, I loose the beginning balance.
Can anybody give me an idea on how I could create a beginning balance when I
select a date range or a starting date?

Only way I can think of eliminating the problem is to store month end
balances. Then you can look up the last balance you need and use it in your
calculations. You would have to union that result with your current/filtered
data and then you should be able to get proper results. Yes, I know what I
recommend contradicts the "never store calculated totals" rule, but in this
case, I think it's worth it.

 

[Anne]

While searching the internet for a solution, I found a similar post as mine
with an answer from Marshal Barton, who suggested creating a temporary table.

I already had a running sum total using DSum. In the query I create a field
which could give me the previous balance, which is the current running
balance less the transaction amount: PriorBal: [RunBal]-[TransAmount]

Upon opening the form, a make table query creates a temporary table with the
running balance everytime the form is opened. This table is used for the
reports. In the form header I show a text box with the "PriorBal" and it
looks really good.