Solved

Need help for auto update a running balance in adotable

Posted on 2014-03-13
2
506 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Suggested Courses
Course of the Month9 days, 11 hours left to enroll

624 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