Sum of column in same table?

[MonkeyWrench768]

Ok everyone, I need your help. I’m trying to create a simple database template for robotics teams in MS Access hopefully to be used in 263s upcoming season, if we have one. I’m using MS Access 2003 and I’m trying to create a Transaction Log table like this.

Description   Amount   Date    Balance
--------------------------------------
Sponsorship   $11,000 9/21/05  $11,000
Registration ($6,000) 9/22/05   $5,000

The thing is, I want the Balance field to automatically fill in by adding the value of Amount to the previous fields Balance value, or if it’s the first entry in the table then setting the balance equal to the amount. Is there any way to do this?

Thanks all.

 

[pbaldy]

Generally speaking, you don't want to save that value at all. Calculate it when you need it on forms/reports. Here are a couple of methods:

http://support.microsoft.com/default.aspx?scid=kb;en-us;208714

There are others that may be more appropriate to your situation. Searching for "running sum" should find some.

 

[MonkeyWrench768]

Well in this particular application we're going to be noting a lot of purchases very quickly, as well as making fast decisions how to spend the money, I'd really like to be able to just fill out a row in the datasheet, type in the amount and have an updated balance be calculated and displayed right there and then. It's quite important in our situation

 

[pbaldy]

Well, you do what you feel is best. I tested, and my running sum query updated each time I added/modified a record.

 

[wkalo]

Statement Of Account Report

Hi There
I Had The Same Problem That You Are Facing And I Found The Solution In An Access Forum. I Hope It Will Solve Your Problem.
Best Of Luck
wkalo