RunningSum Query is a total disaster

[AlexN]

In a database with the structure shown in the attached .jpg file, (for those who do open dbs I attached the database with some data), I created a runningsum query, just like the way I saw it done (or at least as I figured it out) on almost every site, blog, or video tutorial, I’ve searched (even in the ms office support):

I created a TransactionIDAlias field (TransactionID is an autonumber field and the only one with unique values), grouped the TransactionAmount field by SUM, and created the RunTot field with the DSum expression (=DSum("TransactionAmount";"tblTransactions";"[TransactionID]<=" & [TransactionIDAlias] & ""), grouped by Expression.


I had a couple of major issues though:


1. Suppose we have to enter in tblTransactions, a transaction of a previous date, we had forgotten to enter in the appropriate date. The transaction takes the latest transactionID number and appends to the end of the RunningSum query, changing the current runninsum to an unreal value. The same thing happens if the query is sorted by date. This is quite annoying and makes the runningsum value unusable for further calculations or even mere display.


2. When we enter a transaction of a different account in tblTransactions, runningsum query creates a new record with a RunTot value based on the previous one, which belongs to different account though. As you can easily understand this is everything but correct, and mostly frustrating. Same thing happens even if I filter the query by Account.

3. Supposing the RunTot field’s value is the correct one, I tried to show it, in a text box, on another form (AccountDetails) as added information. Tried the DLast expression, even a combination of DLookup-DLast, but achieved only #error? and #Name? messages.


Read everything on this forum that had to do with running sums, searching a hundred times to find anything relevant on the web, tried multiple tests to fix these issues, managed nothing at the end. I’m done with ideas and I’m starting to wonder whether I got the runningsum philosophy all wrong.
Any ideas and guidance will make me grateful for life.


Thanks

 

[plog]

I'm not going to respond to your 3 issues, because I see major issues you need to correct with your tables and maybe they go away if you correct those.

1. There shouldn't be 1-1 relationships in a database. All the data in tblAccounts and tblAccountDetails should be in one table.

2. [OpeningBalance] shouldn't be a field in a table. Instead, each account should get a record in tblTransactions to denote this.

3. tblTransactionCategories should have a numeric field which designates how the transaction effects the balance (-1 or 1). Then you multiple TransactionValue by TransactionAmount to get how it affected the balance.


Those are the issues you should fix with your table structure. I also did look over the rest of your post and see some things wrong with what you attempted in general:

DLast - never use this, it doesn't do what you think it does. Hell, it doesn't do what 99% of people think it does which makes it a horrible function. Most likely you will want to use a Dmax instead.

[TransactionDAlias] used in your criteria argument of your Dsum call is incorrect. What happens when you miss a transaction a month ago and put it in a month later? Your balances are screwed up for 30 days. Instead you probably want to use TransactionDate in the criteria of your DSum because most likely you are grouping by that.

Again, fix the structure issues of the tables first and this might sort itself out.

 

[AlexN]

I'm not going to respond to your 3 issues, because I see major issues you need to correct and maybe they go away if you correct those.
……….
Those are the issues you should fix with your table structure. I also did look over the rest of your post and see some things wrong with what you attempted in general:
………
Again, fix the structure issues of the tables first and this might sort itself out.

Thanks for bothering to answer. (Almost 60 views and only one answer).
I will take your suggestions under serious consideration, although I have some statements to make:

1. There shouldn't be 1-1 relationships in a database. All the data in tblAccounts and tblAccountDetails should be in one table.

That sounds easy, after all, is going to save me a table.

2. [OpeningBalance] should be a field in a table. Instead, each account should get a record in tblTransactions to denote this.

[OpeningBalance] IS a field in a table (tblAccountDetails going to be in tblAccounts)

3. tblTransactionCategories should have a numeric field which designates how the transaction effects the balance (-1 or 1). Then you multiple TransactionValue by TransactionAmount to get how it affected the balance.

Prior to creating the runningsum query I created another one, where I created a field called NetTransactionAmount that gave the appropriate sign (minus or plus) to the TransactionAmount depending to the nature of the transaction, using the IIf expression. Till that stage everything worked fine. I later tried to create the runningsum query using the NetTransaction Amount field. Nevertheless your suggestion seems more practical and most likely I’ll adopt it.

DLast - never use this, it doesn't do what you think it does. Hell, it doesn't do what 99% of people think it does which makes it a horrible function. Most likely you will want to use a Dmax instead.

Dmax never crossed my mind, I cannot, even now, see how it would help but, I’m going to try it. DLast was used after a friend’s suggestion.

[TransactionDAlias] used in your criteria argument of your Dsum call is incorrect. What happens when you miss a transaction a month ago and put it in a month later? Your balances are screwed up for 30 days. Instead you probably want to use TransactionDate in the criteria of your DSum because most likely you are grouping by that.

TransactionID is the only field with unique values in the table, since there will be multiple transactions in a date, and from multiple Accounts. That’s why I used this as an Alias, because every expert gives much consideration to this. Anyway I tried with Date even with AccountID but got errors, I suppose because they have no unique values. And of course the problem you’re referring to, was encountered and mentioned.

[FONT=&quot]Thanks again, you’ve been so kind

[/FONT]

 

[Frothingslosh]

Alex, it looks like you replied so quickly you missed plog's edit. For item 2, what he meant (and edited it to say) was that OpeningBalance should NOT be a field.

DLast simply pulls the final record in the recordset being searched. There is absolutely zero guarantee that it will be the most recent one entered. If you're trying to find the largest value in a recordset, be it dollar amount or date, that should use DMax instead.

Plog is very experienced and very good with Access, so you definitely want to listen to his suggestions.

As to why there were lots of views and no replies, those of us who answer these - and it's typically no more than a dozen or dozen and a half of us - do this in our free time. Long, detailed, multi-part questions with attached databases and diagrams tend to take a long time to work out, so those of us with more limited time will tend to skip over them. Most of the views simply come from people trying to see what problems folks are having and how they get fixed.

 

[plog]

Sorry, i mistyped (and was very slow with my edit), [OpeningBalance] shouldn't be a field in a table. Using my way also allows you to add more transaction types (Interest, Fee, Transfer, etc.) without having to build an Iif statement with every transaction type--just add it to the table with the appropriate 1 or -1.

I don't know what you are trying to convey with the term 'alias' in that second to last paragraph.

 

[AlexN]

Alex, it looks like you replied so quickly you missed plog's edit. For item 2, what he meant (and edited it to say) was that OpeningBalance should NOT be a field.

DLast simply pulls the final record in the recordset being searched. There is absolutely zero guarantee that it will be the most recent one entered. If you're trying to find the largest value in a recordset, be it dollar amount or date, that should use DMax instead.

Plog is very experienced and very good with Access, so you definitely want to listen to his suggestions.

As to why there were lots of views and no replies, those of us who answer these - and it's typically no more than a dozen or dozen and a half of us - do this in our free time. Long, detailed, multi-part questions with attached databases and diagrams tend to take a long time to work out, so those of us with more limited time will tend to skip over them. Most of the views simply come from people trying to see what problems folks are having and how they get fixed.

I never questioned plog’s experience and knowledge. In fact, I consider everyone in this forum more experienced and far better with Access than I am. I’d never question anyone’s experience, specially plog’s who’s posts and replies I admire and respect, learning a lot by.
As for the DMax, what my unexperienced mind thinks (this is not irony), is that it returns the greater value of the field searched. But……I want the current, not the greater. Correct me if I’m wrong.
And finally as for the sole answer comment, yes I admit it was misplaced. Consider it not written.
Thanks

 

[AlexN]

Sorry, i mistyped (and was very slow with my edit), [OpeningBalance] shouldn't be a field in a table. Using my way also allows you to add more transaction types (Interest, Fee, Transfer, etc.) without having to build an Iif statement with every transaction type--just add it to the table with the appropriate 1 or -1.

I don't know what you are trying to convey with the term 'alias' in that second to last paragraph.

Yes, I see now your point and seems brilliant.
Every example or tutorial on creating runningsum queries, uses a field with unique values from the table and calls it Alias, later using it in the DSum expression as criteria.
[FONT=&quot]I’m not familiar with that stuff, I only search and copy what experts suggest. This is not even my spoken language[/FONT]. So I use their terms.

 

[plog]

You didn't say what you were using DLast for, I just saw it you were using it and its my mission in life to spread the word about what a horrible function it is. You'd think (and everyone else does as well) that it does what you were attempting to use it for (it's named 'Last' after all), but no it does not. Horrible horrible function.

To get the most current balance, you wouldn't use a Domain function like DLast or DMax. You'd use 2 queries:

A subquery to determine the most recent transaction date for every account:

SELECT TransactionAccount, MAX(TransactionDate) AS LastTransaction FROM tblTransactions GROUP BY TransactionAccount;

Then you'd link that subquery to you're running balance query. You would link TransactionAccount to TransactionAccount and LastTransaction to TransactionDate. That would identify the record of the last transaction and allow you to identify the balance on that date.

 

[AlexN]

You didn't say what you were using DLast for, I just saw it you were using it and its my mission in life to spread the word about what a horrible function it is. You'd think (and everyone else does as well) that it does what you were attempting to use it for (it's named 'Last' after all), but no it does not. Horrible horrible function.

To get the most current balance, you would use a Domain function like DLast or DMax. You'd use 2 queries:

A subquery to determine the most recent transaction date for every account:

SELECT TransactionAccount, MAX(TransactionDate) AS LastTransaction FROM tblTransactions GROUP BY TransactionAccount;

Then you'd link that subquery to you're running balance query. You would link TransactionAccount to TransactionAccount and LastTransaction to TransactionDate. That would identify the record of the last transaction and allow you to identify the balance on that date.

Wow!
Subqueries and relationships between queries. This is going far beyond my experience and capacity. Thrilling though. Seems I have a lot of reading to do.

Thanks

 

[AlexN]

Well, created the subquery, it wasn't something I didn't knew already but never named it "subquery", but....
have nothing to link it with, there is no runningsum query, I can't get to make it work.

If not tired already suggestions would be welcomed, otherwise I should just leave it to the professionals, as implied by a friend a few days ago.

Thanks everybody.

 

[plog]

Can you post a database with your tables and the queries you referenced?

 

[AlexN]

Can you post a database with your tables and the queries you referenced?

Coming in a few minutes. Thanks

 

[AlexN]

Can you post a database with your tables and the queries you referenced?

There it is...

 

[plog]

To get the balance by day you will need a sub-query. Paste the below into a query and save it with the name 'RunningBalances_sub1':

SELECT tblTransactions.TransactionAccount, tblTransactions.TransactionDate, Sum([TransactionAmount]*[TransactionFactor]) AS TransactionValue
FROM tblTransactionCategories INNER JOIN tblTransactions ON tblTransactionCategories.TransactionCategoryID = tblTransactions.TransactionCategory
GROUP BY tblTransactions.TransactionAccount, tblTransactions.TransactionDate;

It groups all the transactions by day and converts them into debits/credits to make them add up correctly. Then, to get the balance per account per day use this SQL:

SELECT RunningBalances_sub1.TransactionAccount, RunningBalances_sub1.TransactionDate, DSum("TransactionValue","RunningBalances_sub1","[TransactionAccount]=" & [TransactionAccount] & " AND [TransactionDate]<=#" & [TransactionDate] & "#") AS Balance
FROM RunningBalances_sub1;

From there you use my previous method for getting the most recent balance.

 

[AlexN]

To get the balance by day you will need a sub-query. Paste the below into a query and save it with the name 'RunningBalances_sub1':

SELECT tblTransactions.TransactionAccount, tblTransactions.TransactionDate, Sum([TransactionAmount]*[TransactionFactor]) AS TransactionValue
FROM tblTransactionCategories INNER JOIN tblTransactions ON tblTransactionCategories.TransactionCategoryID = tblTransactions.TransactionCategory
GROUP BY tblTransactions.TransactionAccount, tblTransactions.TransactionDate;

It groups all the transactions by day and converts them into debits/credits to make them add up correctly. Then, to get the balance per account per day use this SQL:

SELECT RunningBalances_sub1.TransactionAccount, RunningBalances_sub1.TransactionDate, DSum("TransactionValue","RunningBalances_sub1","[TransactionAccount]=" & [TransactionAccount] & " AND [TransactionDate]<=#" & [TransactionDate] & "#") AS Balance
FROM RunningBalances_sub1;

From there you use my previous method for getting the most recent balance.

Well you certainly help me open my eyes, apart from quick it was very educational. I envy you. But take a look in the attached file to see what I finally get. Does it feel right? Something is not going well I think. I'm going to search though, there's a whole new world there for me.

No words can express my gratitude.

 

[plog]

There shouldn't be null values. I'd investigate the sub query to see why dates are appearing without values.

 

[AlexN]

There shouldn't be null values. I'd investigate the sub query to see why dates are appearing without values.

Not only that. There can't be the same value for a two month period, since there are transactions almost everyday.

 

[AlexN]

Well, this might help
Made the RunningBalances_sub1 query, a CreateTable query and thus created a table (tblRunningBalances) to which I added an autonumber field (ID) as a primary key.
Then, in qryBalance, I used the tblRunningBalances instead of the RunningBalances_sub1 query as a row source, and changed the TransactionDate in criteria with ID. The query worked fine.
But…..there’s always a “But”………
Every time the RunningBalances_sub1 query runs, it deletes tblRunningBalances, and creates a new one which of course lacks the ID field.
Not a solution but might lead you to one……


Needless to say I spent all night yesterday testing and trying things , only to come to the conclusion that, for some reason unknown, we shouldn’t use TransactionDate in DSum criteria.

Edit.....
On second thought....
If we changed now the RunningBalance_sub1 query to an AppendQuery, wouldn't it be good?

 

[plog]

No. APPEND and MAKE TABLE queries are crutches for people who have poor abstract reasoning and a bad grasp of how data works. Ultimately APPEND and MAKE TABLE queries rely on a SELECT query to populate them, so you might as well use a SELECT query.

If you want balance by date, then you need to use a date. My guess is you either have invalid/NULL dates in TransactionDate. Is TransactionDate an actual Date/Time field or did you make it a text data type?

You need to debug the sub-query I gave you to see where and why it fails to generate values.

 

[AlexN]

If you want balance by date, then you need to use a date. My guess is you either have invalid/NULL dates in TransactionDate. Is TransactionDate an actual Date/Time field or did you make it a text data type?

You need to debug the sub-query I gave you to see where and why it fails to generate values.

Ok, I said I'm inexperienced, I'm not an idiot. Of course TransactionDate is a Date field and apart from this, is set to Required so there are no Null Values. But even more, while testing, just to be sure, I added a field in sub-query with the CDate expression and used this in criteria, just to have the same result.

Debug the sub-query? That's a new unknown word....have to run to my books again......


Thanks