Solved

Need help for auto update a running balance in adotable

Posted on 2014-03-13
2
493 Views
Last Modified: 2014-03-15
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
Comment
Question by:Brainwashed2
2 Comments
 
LVL 27

Accepted Solution

by:
Sinisa Vuk earned 500 total points
ID: 39927760
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
 

Author Closing Comment

by:Brainwashed2
ID: 39929311
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question