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

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
Brainwashed2Asked:
Who is Participating?
 
Sinisa VukCommented:
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
 
Sinisa VukCommented:
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
 
Brainwashed2Author Commented:
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
 
Brainwashed2Author Commented:
Thank you again. You are the greatest :)
0
 
Brainwashed2Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.