Running Total

[mike60smart]

Hi Everyone

Can anyone show me how to get a Running Total in the attached Form that opens at startup.

I need the Running Total of "CalcTodaysValue" to be displayed in the "Balance" Control

Any help appreciated.

 

[Gasman]

Use Dsum() ?
I cannot open the DB, too late a version for me.

 

[CJ_London]

not following - balance is a single control, so don't see where running sum comes into it. Just use dsum as gasman suggested - with appropriate criteria. Running sums can be a drain on performance

 

[Pat Hartman]

You don't need a domain function. That runs a separate query. Just add a Sum(somefield) to a textbox on the form's footer. This doesn't even need to be visible if it is a subform, you can copy the sum from the subform so it shows on the mainform.

 

[mike60smart]

You don't need a domain function. That runs a separate query. Just add a Sum(somefield) to a textbox on the form's footer. This doesn't even need to be visible if it is a subform, you can copy the sum from the subform so it shows on the mainform.

Hi Pat
Your suggestion is the best option I believe. Will try to convince the Op also.
Many thanks

 

[Gasman]

While Pat's suggestion is likely the best option, I doubt the O/P is bothered how it is done?, they just want to see it?

 

[MikeT1941]

Hello All- I'm the OP that Mike was helping originally.
Thanks to all of you above for your advice.
I've continued working on the DSum method. This is because I want a line by line total so I can calculate the interest on the balance outstanding ( even if it is a long time ago). No doubt a report will be required in due course!

At the moment the Balance field uses:
BalTrial: CCur(DSum("[CalcTodaysValue]","BillsIssuedAndPaidQuery","[ID]<= " & [PKID] & " And [HistoryID]=" & [HistoryID]))

Some of the accounts work fine with this and some don't. This is because the criteria above are not filtering out values from other different accounts, which I would want them to. Instead it adds in the values for the intervening PKID values, however, where the PKID values are consecutive and ascending there is no problem. Do I have to generate a fresh PKID field for each run of the query?

Please could someone tell me where I'm wrong.

Mike

 

[Gasman]

So add account criteria?

 

[MikeT1941]

Sorry Gasman, I should have explained that the HistoryID is the account number- if I understand you correctly.
Each client had a ledger page/part of and the HistoryID is related to that set of ledger entries

 

[Gasman]

Sorry Gasman, I should have explained that the HistoryID is the account number- if I understand you correctly.
Each client had a ledger page/part of and the HistoryID is related to that set of ledger entries

So I cannot see how it would include other accounts in that case?

 

[MikeT1941]

PKID is the identifier for each ledger entry e.g. for [HistoryID]=555: Work on house £100 is PKID 223 and payment is PKID 245 because other ledger entries take place in the meantime. However, we do not want the values from 224 to 244 including in the totals for HistoryID 555 because they belong to another job. At the moment they are- which is the purpose of my asking.

 

[Gasman]

So job would be another criteria?

 

[MikeT1941]

Fraid not. I used the words job and account as alternatives. Although they are- it was wrong of me

 

[MikeT1941]

Further to post #7 it is not possible to generate a new PKID field as it is dependent on the original ID field. Please forget the suggestion- for the moment

 

[Gasman]

Well, if you can identify that certain records, should not be included, then it should be possible to code for that, but you are not being clear as to how that is done.

 

[Gasman]

Looking at your code again, I would be using the form control values?, Me.whatever to compare to.

 

[MikeT1941]

Thanks, Gasman. I have tried using Me.HistoryID with various combinations of square and round brackets, with and without quotes. All rejected, I'm afraid.
Herewith two screenshots, one of "working", when the PKID's are adjacent and "notworking", when they are not.
In the latter, the total in the balance column for line 2 ( PKID/ID 33) includes the total of PKID values for 30,31, and 32 as well. Other lines are the same. I'm sorry I can't explain it better.

 

[MikeT1941]

Oops!

 

[CJ_London]

the only point of ordering by date then ID is when the dates are the same otherwise you get the problem you have identified when an earlier date is added at a later time. The solution is to create a temporary 'key' by combining the two columns together - in this case date and id. The problem then is that dates are numeric being the number of days since 31/12/1899 - prior to this date the value will be negative. So suggest in your case you can calculate it as a key in your source query

key: format(trandate, "yyyymmdd") & format(ID,"00000")

I've made the ID 5 chars because although you are on small number right now, they will grow. You may need to make it more.

so your record ID 33 becomes "1838050100033"
and for ID 30 becomes "1839050100030"

you may need to make this numeric for faster performance e.g.

key: cSng(format(trandate, "yyyymmdd") & format(ID,"00000"))

and your dsum becomes

BalTrial: CCur(DSum("[CalcTodaysValue]","BillsIssuedAndPaidQuery","key<='" & key & "'")


or if numeric

BalTrial: CCur(DSum("[CalcTodaysValue]","BillsIssuedAndPaidQuery","key<=" & key)

edit, may need to include your historyID as well

BalTrial: CCur(DSum("[CalcTodaysValue]","BillsIssuedAndPaidQuery","key<='" & key & "' and HistoryID=" & historyid)

 

[MikeT1941]

Thanks CJ- I was just shutting down so will try that in the morning. Much appreciated. Mike
There are 600 pags in the ledger- I've only entered 50 and not entered every detail of them -so there may well be loads and loads! Oh for a quill pen.