Query der beregner saldo løbende

Hi Hugo lang time no hear :-)

I'm not I to debit/credit, but could I be an idea to update the saldo field when creating the record rather than once in a while?

Editor isn't working very well today :-(

"I'm not in to ...

And I'm sure it could be done with a query to calculate the running saldo in a query too.

Can you give and example of your data?

Hi Terry

Yes long time no hear. Since I got sick I haven't been very active here at eksperten.dk

I have a table you can look at in
https://dl.dropboxusercontent.com/u/65392149/Database1.zip

What I do now is what you write in #1, but if I the have to insert a transaction from 2 days ago I still have to recalc the saldo after the new record in order to get it right.
I have used many hours thinking of what I could do to get it running faster and easier. But haven't 'fundet de vise sten' :-)
It is a Delphi program that runs as frontend to the database and a lot of the data updates are done by tasks running in the background. but this one here I have had no success with. So now I am back to a query.

sorry to hear you have been sick Hugo, hope your better now.

I'll have a look at dB and get back to you asap

Can you tell me a little about the data?

I can see which fields are debit and credit and saldo, but how do I sort?
In query I can see that fldDate is used to sort, can I use this and is it unique?
If not then how do I know correct order of transactions?

Is there anything else I need to take into concideration?

I wouldn't say I am better now but I am fighting the cancer the best I can. But not always easy

flddate is the date of the transaction and fldcreated_date is the date and time of creating the record

The sort has to be flddate and then fldcreated_date
That is the only important to  take into concideration

Well I'm keep my fingers crossed for you Hugo.

Just want to make sure     I'm calculating correctly. In my heasd debit is minus and credit is plus, so if I assume
that is correct I add the debit and credit fields then add result to saldo from previous transaction.

Example from first few rows in query.


flddate    fldcreated_date        flddebit    fldkredit    fldsaldo
03-Jan-11    14-Nov-11    1,000.00    0.00        -1,000.00
04-Jan-11    14-Nov-11    0.00        400.00        600.00
04-Jan-11    14-Nov-11    0.00        200.04        800.04

Oops
flddate    fldcreated_date        flddebit    fldkredit    fldsaldo
03-Jan-11    14-Nov-11    1,000.00    0.00        -1,000.00
04-Jan-11    14-Nov-11    0.00        400.00        -600.00
04-Jan-11    14-Nov-11    0.00        200.04        -399.96

Hugo, I have a few ideas but stopping for today, will look at it again tomorrow.

Almost correct - this type of account debit=income and kredit=expense

fldcreated_date is date and time (time is very important)

Try this.

Make a query (qryfs_transactions)

SELECT T1.flddate AS DT, T1.fldcreated_date AS CDT, [flddebit]*-1 AS DEB, T1.fldkredit AS KRE, [DEB]+[KRE] AS RowSum
FROM tblfs_transactions AS T1
ORDER BY T1.flddate, T1.fldcreated_date;


Then make another query (qrySaldo)
SELECT T.DT, T.CDT, T.DEB, T.KRE, (SELECT SUM(RowSum) FROM qryfs_transactions QF WHERE QF.DT <= T.DT AND QF.CDT <= T.CDT) AS Saldo
FROM qryfs_transactions AS T
ORDER BY T.DT, T.CDT;

Probably could do it in one query but not much time right now

"this type of account debit=income and kredit=expense" !

Missed that, so it will require you to alter first query so that you multiply fldkredit with -1 instead of flddebit.

The saldo isn't calculated correct
I have put a new one in the dropbox and if you open qryfs_saldo and loops the records you will see several times where saldo isn't correct. The last record and some where there is drawn 4000
It has something to do with the dates I am sure. The 4000 are some fixed expenses that are created maybe once or twice a year and they make something strange happen

Hugo, can you give me a bit more information as to how I find the incorrectly calculated saldo? There are 732 records so dont want to have to manually calculate each. Also an explanation as to why they are not correct.

In the VBA code which currently calculates there must be some logic which knows when and how to make the calculation.

So as much information as possible to help me understand how to make calculation.

If you look at records with 4000 kredit you will find the wrong calc

It is actually not VBA that I use today but Delphi
I loop the table sorted by flddata, fldcreated_date and the I calc each record and saves the result back to the table.

are you sure its only where 4000 kredit is calculated?
Very likely something to do with dates and how I'm sorting

I think I see the problem and maybe have a solution too.

Think this helps but no guaranties

SELECT T.DT, T.CDT, T.DEB, T.KRE, T.RowSum, (SELECT SUM(RowSum) FROM qryfs_transactions QF WHERE QF.DT < T.DT OR (QF.DT = T.DT AND QF.CDT <= T.CDT) ) AS Saldo
FROM qryfs_transactions AS T
ORDER BY T.DT, T.CDT;

It looks a lot better but I haven't been at the computer for some days so I will test it and get back to you if that is OK

Hi Hugo, dont you worry about this, take care of yourself.

BR
Terry

I have just tried the latest query on a full set of data and it looks like it is about 9000 of course
I have exported my data to Excel and it is the same. The query is about 9000 of course
I will try to analyze a bit more and try to find out where it goes wrong.

I have found the 'error' I think
There was one row with data where there was a null value in kredit field. That messed everything up.
Make an answer for well earned points.

Here it is Hugo :-)

thanks a lot.