 |
Nikkap
Level: Trainee
Registered: 17-03-2006
Posts: 2
|
Accumulated Sum , Credit ,Debit , Balance in Database
I am new in programming in vb.net . I use visual studio 2005.
I have made an sql database with four columns : date,credit,debit,balance. In a windows form i use a data grid to display the data.
I would like to insert in data grid only date and debit or credit and after that display automatic the balance up to the inserted date.
for example the data grid is like this
Date Debit Credit Balance
1/1/2006 200,00 200,00
2/2/2006 300,00 500,00
3/3/2006 100,00 400,00
when i insert a new row
date -> 01/03/06 credit -> 50,00 to display as balance 450,00
Sorry for my english, Please if someone can help me how to do this i would be grateful.
Thanks in andvance
|
|
17-03-2006 at 02:45 PM |
|
|
Goran
Level: Moderator
Registered: 16-05-2002
Posts: 1681
|
Re: Accumulated Sum , Credit ,Debit , Balance in Database
And you dont know to do what exactly: insert the data in datagrid, or you dont know how to calculate balans? Should be easy, all you need to do is to read the balance from the last row, and then when adding new row to table, just substract credit value from the balanse... You shouldnt be storing balance in a table also, since if you change a value for credit in, let say 11th record, then you will need to recalculate all the balance after the 11th row...
____________________________
If you find the answer helpful, please mark this topic as solved.
|
|
20-03-2006 at 01:38 AM |
|
|
Nikkap
Level: Trainee
Registered: 17-03-2006
Posts: 2
|
Re: Accumulated Sum , Credit ,Debit , Balance in Database
quote:
Goran wrote:
And you dont know to do what exactly: insert the data in datagrid, or you dont know how to calculate balans? Should be easy, all you need to do is to read the balance from the last row, and then when adding new row to table, just substract credit value from the balanse... You shouldnt be storing balance in a table also, since if you change a value for credit in, let say 11th record, then you will need to recalculate all the balance after the 11th row...
Thanks for your reply.I have created the database and the datagrid displayed in my form.That is okey . The question is that the user have to enter date and debit or credit and the field balance to diplay automatic the appropiate value
let's Assume that this is what displayed in my datagrid
Date Debit Credit Balance
01/01/06 100,00 100,00
01/02/06 200,00 300,00
02/02/06 100,00 200,00
if now I add this record in datagrid
date -> 15/01/06 and credit -> 50,00
the datagrid must be recalculated
01/01/06 100,00 100,00
01/02/06 200,00 250,00
02/02/06 100,00 150,00
15/01/06 50,00 50,00
and if I sort the column date, would be fine
01/01/06 100,00 100,00
15/01/06 50,00 50,00
01/02/06 200,00 250,00
02/02/06 100,00 150,00
how to define the field balance ? as calculated field in the same table ? and how its possible to recalculate all the records after the inserted date?
Sorry if cannot be understood
thanks.
|
|
20-03-2006 at 12:11 PM |
|
|
Goran
Level: Moderator
Registered: 16-05-2002
Posts: 1681
|
Re: Accumulated Sum , Credit ,Debit , Balance in Database
quote:
the datagrid must be recalculated
01/01/06 100,00 100,00
01/02/06 200,00 250,00
02/02/06 100,00 150,00
15/01/06 50,00 50,00
I dont understant why you would display to a user data that are not sorted. This kind of view can only confuse the user. I believe you should immediatelly sort the data, and point to a newly inserted row.
As for calculating running total, I will give you an example how it works. In order to work in your example, date field must be unique. It shoiuld be somthing like
| SELECT [date field], debit, credit, (SELECT SUM(debit-credit) FROM TableName b WHERE b.[date field]>=a.[date field]) FROM TableName a ORDER BY a.[date field] |
I believe you are getting the idea....
____________________________
If you find the answer helpful, please mark this topic as solved.
|
|
22-03-2006 at 10:31 AM |
|
|
|
|
 |
|
