?
Solved

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

Posted on 2014-03-18
5
Medium Priority
?
348 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 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

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month12 days, 15 hours left to enroll

777 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