Avatar billede hugopedersen Nybegynder
20. februar 2016 - 10:31 Der er 26 kommentarer og
1 løsning

Query der beregner saldo løbende

Jeg har en tabel med en masse kontotransaktioner hvor jeg som det er nu har noget kode der løber alle poster igennem sorteret på transaktionsdato og indtastningsdato.
Koden tager så debit beløb, trækker kredit fra og gemmer resultatet i saldo felt. Det gør den så hele vejen igennem alle transaktioner.
Men som der bliver flere og flere transaktioner i tabellen, så bliver det mere og mere tidskrævende.
Jeg tænker så på om man ikke kan skrue et par queries sammen der kan løse dette så jeg kan spare den opdatering i tabellen.
Avatar billede terry Ekspert
20. februar 2016 - 11:22 #1
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?
Avatar billede terry Ekspert
20. februar 2016 - 11:24 #2
Editor isn't working very well today :-(

"I'm not in to ...
Avatar billede terry Ekspert
20. februar 2016 - 11:28 #3
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?
Avatar billede hugopedersen Nybegynder
20. februar 2016 - 11:55 #4
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.
Avatar billede terry Ekspert
20. februar 2016 - 12:28 #5
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
Avatar billede terry Ekspert
20. februar 2016 - 12:41 #6
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?
Avatar billede hugopedersen Nybegynder
20. februar 2016 - 12:42 #7
I wouldn't say I am better now but I am fighting the cancer the best I can. But not always easy
Avatar billede hugopedersen Nybegynder
20. februar 2016 - 12:45 #8
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
Avatar billede terry Ekspert
20. februar 2016 - 13:15 #9
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
Avatar billede terry Ekspert
20. februar 2016 - 13:18 #10
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
Avatar billede terry Ekspert
20. februar 2016 - 15:46 #11
Hugo, I have a few ideas but stopping for today, will look at it again tomorrow.
Avatar billede hugopedersen Nybegynder
20. februar 2016 - 16:01 #12
Almost correct - this type of account debit=income and kredit=expense

fldcreated_date is date and time (time is very important)
Avatar billede terry Ekspert
20. februar 2016 - 16:46 #13
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;
Avatar billede terry Ekspert
20. februar 2016 - 16:46 #14
Probably could do it in one query but not much time right now
Avatar billede terry Ekspert
21. februar 2016 - 13:29 #15
"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.
Avatar billede hugopedersen Nybegynder
21. februar 2016 - 20:32 #16
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
Avatar billede terry Ekspert
22. februar 2016 - 08:39 #17
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.
Avatar billede hugopedersen Nybegynder
22. februar 2016 - 11:05 #18
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.
Avatar billede terry Ekspert
22. februar 2016 - 12:09 #19
are you sure its only where 4000 kredit is calculated?
Very likely something to do with dates and how I'm sorting
Avatar billede terry Ekspert
22. februar 2016 - 12:26 #20
I think I see the problem and maybe have a solution too.
Avatar billede terry Ekspert
22. februar 2016 - 14:39 #21
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;
Avatar billede hugopedersen Nybegynder
26. februar 2016 - 10:27 #22
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
Avatar billede terry Ekspert
26. februar 2016 - 10:57 #23
Hi Hugo, dont you worry about this, take care of yourself.

BR
Terry
Avatar billede hugopedersen Nybegynder
28. februar 2016 - 11:09 #24
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.
Avatar billede hugopedersen Nybegynder
28. februar 2016 - 18:24 #25
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.
Avatar billede terry Ekspert
29. februar 2016 - 09:55 #26
Here it is Hugo :-)
Avatar billede terry Ekspert
29. februar 2016 - 13:19 #27
thanks a lot.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Dyk ned i databasernes verden på et af vores praksisnære Access-kurser

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester