Solved

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

Posted on 2014-03-18
5
346 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
[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
  • 3
  • 2
5 Comments
 
LVL 27

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 27

Accepted Solution

by:
Sinisa Vuk earned 500 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

Independent Software Vendors: 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

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

691 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