Hi to all.

First of all, thanks for reading this.
I program morely on databases. now, i usually encounter this problem for so many years now and this is it. supposed i have a table in my database... column 'income' and 'expense'. upon the execution of the SQL command, another column is added virtually to the data and lets name it the 'running balance'.

as the name implies, i need a running balance on my application. it is better to illustrate it than explain. below is a sample data. let's say the table is shown on a datagrid

date      Income   Expense   Running Balance

2/1/2004  1000               1000
2/1/2004  500                1500
2/3/2004           250       1250
2/4/2004  600                1850
2/4/2004           50        1800

the running balance column is not part of the underlying database but merely created at run time and disposed of after the termination of the application.

now, i have been using a workaround for this problem for a long time
-> on any changes on the table, i will always recompute the whole table which requires:
1> going to the beginning of the table,
2> recompute each data row until i reach the end of the table
3> and updating each row for the correct values.

my strategy works fine, but if the table grows big, say for example a YEARs record of income & expense....., it is taking an awful lot of time just recomputing the values.

I don't have problems on reports because i will only have to recompute the whole data once and everything will be fine. also crystal reports supports this feature.

the running balance is generally not required to have a good running database application, but my clients seems to have become fond of this feature for a long time and it sorts of entertains them.

i am wondering if there is another approach, suggestion or solution to this problem of mine that would make my program runs faster apart from upgrading my clients' computers as the database of my clients is now getting bigger and bigger each day and my programs runs a little more slowly each day. I am only talking of seconds here. a 1 to 2-second delay each time you enter/edit values on the field is too much already when you are entering series of data. i don't want to disable this feature just to make my program run faster as my happy clients would surely miss them.

this running balance feature is best seen on the 'Microsoft Money'. if you have used this software, you will exactly know what i am talking about.

i hope you could share your views to my question. i appreciate it very much. thanks again.
VCG [/size]

: Hi to all.
:
: First of all, thanks for reading this.
: I program morely on databases. now, i usually encounter this problem for so many years now and this is it. supposed i have a table in my database... column 'income' and 'expense'. upon the execution of the SQL command, another column is added virtually to the data and lets name it the 'running balance'.
:
: as the name implies, i need a running balance on my application. it is better to illustrate it than explain. below is a sample data. let's say the table is shown on a datagrid
:
:

: date      Income   Expense   Running Balance
: 
: 2/1/2004  1000               1000
: 2/1/2004  500                1500
: 2/3/2004           250       1250
: 2/4/2004  600                1850
: 2/4/2004           50        1800
: 

:
: the running balance column is not part of the underlying database but merely created at run time and disposed of after the termination of the application.
:
: now, i have been using a workaround for this problem for a long time
: -> on any changes on the table, i will always recompute the whole table which requires:
: 1> going to the beginning of the table,
: 2> recompute each data row until i reach the end of the table
: 3> and updating each row for the correct values.
:
: my strategy works fine, but if the table grows big, say for example a YEARs record of income & expense....., it is taking an awful lot of time just recomputing the values.
:
: I don't have problems on reports because i will only have to recompute the whole data once and everything will be fine. also crystal reports supports this feature.
:
: the running balance is generally not required to have a good running database application, but my clients seems to have become fond of this feature for a long time and it sorts of entertains them.
:
: i am wondering if there is another approach, suggestion or solution to this problem of mine that would make my program runs faster apart from upgrading my clients' computers as the database of my clients is now getting bigger and bigger each day and my programs runs a little more slowly each day. I am only talking of seconds here. a 1 to 2-second delay each time you enter/edit values on the field is too much already when you are entering series of data. i don't want to disable this feature just to make my program run faster as my happy clients would surely miss them.
:
: this running balance feature is best seen on the 'Microsoft Money'. if you have used this software, you will exactly know what i am talking about.
:
: i hope you could share your views to my question. i appreciate it very much. thanks again.
: VCG [/size]
:

Is it possible to store the running balance in a separate table linked to the transactions table by the transaction primary key id? If I understand it correctly, each transaction has a corresponding running balance value.

TableOne:
ID      Date    Income   Expense
1      2/1/2004  1000            
2      2/1/2004  500  
3      2/3/2004           250   
4      2/4/2004  600            
5      2/4/2004           50       

TableTwo:
ID     RunningBalance
1       1000
2       1500
3       1250
4       1850
5       1800

The use a join to link them up together:

Select TableOne.Date, TableOne.Income, TableOne.Expense, TableTwo.Running Balance
From TableOne Inner Join TableTwo On
TableOne.ID = TableTwo.ID