• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

Need help for auto update a running balance in adotable

Hi experts, I feel empty and searching with google was too long without a result I understood.

Perhaps you can help me.

My Problem:

I habe an ADOTable with the Fields 'ID(unique)', 'Date', 'Title', 'Comment', 'Account' and 'amount'.
In a DB Grid I want to complete with one more column named 'balance'. It's no problem, to add this calculation for every row, but then I only get the sum of this row. I would like to get a cumulative result. And if I will later add an entry for an earlier date, all balance should be autoupdtaed like in Excel.

i.e.

beginning balance := 1000


Date                    |  Title  |...  amount ... |  balance

01.01.2014          |Test1  | .... 100,00  |  1100
05.01.2014          |Test2  | .... -50,00  |   1050
05.02.2014          |Test3  | .... -100,00  | 900
05.02.2014          |Test4  | .... -200,00  |  700
05.03.2014          |Test5  | .... -800,00  |  -100

and so on...

Have you some code to rescue me ?

Thanks, Tom
0
Brainwashed2
Asked:
Brainwashed2
1 Solution
 
Sinisa VukCommented:
If you want to show calculated field - I can suggest this:
var
  fStartAmount: Double;
begin
  fStartAmount := 1000;    //
  //auto calc field Balance
  ADODataSet1.Close;
  ADODataSet1.CommandText :=
    'select Id, "Date", Title, Comment,Account,amount, :pStartBalance + '+
    '(select sum(tbl2.amount) from Tabel1 as tbl2 where tbl2."Date"<=Tabel1."Date" '+
    'and tbl2.Id<=Tabel1.Id) as Balance '+
    'from Tabel1 order by "Date", Id';
  ADODataSet1.ParamCheck := True;
  ADODataSet1.Parameters.ParamByName('pStartBalance').Value := fStartAmount;
  ADODataSet1.Open;

Open in new window

.. do not use AdoTable but AdoDataset. This way you can do make calculated field in sql statement. I assume that date is sorted as Id too.


...or you can use some memory dataset which will load original dataset and additional dataset Balance can be calculated in while loop.
0
 
Brainwashed2Author Commented:
Thank you very much :) You freed me from hamster wheel and rescued my weekend.

... but spoke to soon :(

The solution is working fine, as long as I don't append data (with higher id) before earlier entries. Then the result is like this:

StartAmount -1000

ID   |  Date           Comment   Amount    Balance

103  |24.02.2014   | Test1   |   -500      |   -1500
76    |12.03.2014   | Test2   |   -100      |   -1100  <<< !!
104  |13.03.2014   | Test3   |   +2000   |      400  
77    |15.03.2014   | Test4   |   -600      |   -1700  <<< !!
101  |15.03.2014   | Test5   |   +600     |   -1100
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now