Solved

Need help for auto update a running balance in adotable

Posted on 2014-03-13
2
474 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 25

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

759 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