Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need help for auto update a running balance in adotable - Part II

Posted on 2014-03-18
5
Medium Priority
?
351 Views
Last Modified: 2014-03-19
Part I: http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_28387628.html?anchor=a39927760

Hi again. Last solution of  Sinisa Vuk is great, but now, a new problem appeared.
Calculated fields are correct, as long as I don't append data (with higher id) before earlier entries. In this case, 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

I get this by using the following way of Sinisa:
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


Is there anybody who can help me one more time?

Thanks in advance,
Tom
0
Comment
Question by:Brainwashed2
  • 3
  • 2
5 Comments
 
LVL 28

Expert Comment

by:Sinisa Vuk
ID: 39936574
Yes, you're right - my example need stable unique value to make good balance value (in subselect). Which database you use? Problem is that we need separate each row for same date.
0
 

Author Comment

by:Brainwashed2
ID: 39936621
I'm using simple Access.accdb.  Application is only for a local Budgetplan with some special features und for sync (csv import/export) with mobile phone.
0
 
LVL 28

Accepted Solution

by:
Sinisa Vuk earned 2000 total points
ID: 39936844
Ok, I build "unique index" using date field and ID as autoincrement value. This index is "special" formatted to string for sorting and comparing purpose. I assume that under some date - all rows with same date - are equal - so can be sorted by incremental ID.
It will be more easier if date contains time part already.

SELECT Format(DateAdd('s',ID,[Date]), 'yyyy-mm-dd hh:nn:ss') AS UniqueID, ID, [Date], title, Comment, Account, Amount,
100 +  (select sum(tbl2.Amount) from BudgetPlan as tbl2 where Format(DateAdd('s',tbl2.ID,tbl2.[Date]), 'yyyy-mm-dd hh:nn:ss')<=Format(DateAdd('s',BudgetPlan.ID,BudgetPlan.[Date]), 'yyyy-mm-dd hh:nn:ss')) as Balance 
FROM BudgetPlan order by 1

Open in new window

(note - this is build in ms access - but can be used in Delphi like older example with parametric start balance)
0
 

Author Closing Comment

by:Brainwashed2
ID: 39938991
Thank you again. You are the greatest :)
0
 

Author Comment

by:Brainwashed2
ID: 39939990
Sorry, still no end for now. Why?
... and although it works, there is a new problem. I made an import with 500 records. So new access.accdb is only 460 kb but now running ADODataset with AutoCalcFields needs more than 14 !! seconds to update the balance.

to be continued here: http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_28392339.html
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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

772 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