Solved

Need help for auto update a running balance in adotable

Posted on 2014-03-13
2
478 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 26

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now