Solved

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

Posted on 2014-03-18
5
332 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 25

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 25

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now