asked on

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

Part I: https://www.experts-exchange.com/questions/28387628/Need-help-for-auto-update-a-running-balance-in-adotable.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

Sinisa Vuk

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.

Brainwashed2

ASKER

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.

ASKER CERTIFIED SOLUTION

Sinisa Vuk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brainwashed2

ASKER

Thank you again. You are the greatest :)

Brainwashed2

ASKER

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: https://www.experts-exchange.com/questions/28392339/Need-help-for-auto-update-a-running-balance-in-adotable-Part-III.html