Running sum in union & Crosstab Query (1 Viewer)

[ahmad_rmh]

How to get running sum in union and crosstab query?

 

[Gasman]

I believe you would need another query with the union as it's source, and crosstab from that?

 

[ahmad_rmh]

Crosstab is from union query

I believe you would need another query with the union as it's source, and crosstab from that?

 

[ahmad_rmh]

But the question is, what would be the formula for running sum?

 

[Gasman]

Search here for those exact words.
Has to be carried out in a single query, hence the extra query.

 

[theDBguy]

But the question is, what would be the formula for running sum?

A running sum usually involves a subquery or a domain function. Check out the links in the similar threads list down below.

 

[CJ_London]

Still not prepared to take the experts advice?

The basic formula would be something like this in a query based on the union query

Runningsum: dsum(“qty”,”unionqry”,”prodid=“ & prodid & “ and trandate <=“ & trandate)

but this will only work providing each product does not have any duplicate trandates, if so you need to find another field or fields to create uniqueness

union queries lose their indexing so this will be very slow - an old client presented me with something similar to do with sales commissions -around 10k transactions - their existing union query took over 6 hours to run

You will better to use your union query to populate a single table, apply indexing, then calculate your running sim

 

[Pat Hartman]

A running sum requires a unique identifier. It can be done most efficiently with a self join. Domain functions run a separate query for every single row. If you have a thousand rows, you are running a thousand dsum()'s. This gets slow in a hurry. With a self join the database engine does the heavy lifting.

The running sum is trivial and fast when done on a report. It is expensive and slow when done with a query.

 

[gemma-the-husky]

The issue with a running sum is that it depends on the sort order of the records. If that changes the running sum needs to be re-evaluated/re-calculated. A report has a fixed order, so it can be calculated as each row is printed, and will not then change.

The underlying reason it's hard is that a set of records (a recordset) is a collection of individual records. Therefore for each record the next and previous records are not defined. Because there is no concept of a "next" record for a data set, there is just no such thing as a simple running sum of which you can keep track. So there is no formula for a running sum.

So although you could find an inefficient way to do it, it's best not to bother, and just consider, for example, the total of the set of records, rather than the partial total evaluated as you navigate through the records. That way, you work with what you have.

In the case of a collection of stock records, it ought to be enough that the entire collections sums to 12,500 widgets, and not bother about what the partial total was after each individual transaction that comprised the total of 12,500. What does it matter that the total WAS 12,520, and you just reduced that total by 20 with the last sale record. If you sorted it by transaction amount, rather than date, the running total declares differently, but the final total is still the same. Hence concentrate on the final total.

With a trial balance in a ledger, for instance, you just need to know the total for each account, and that the overall total of all the transactions is zero. When the ledger was maintained manually, you would have to sum the figures as you went, but we handle the records differently and more flexibly now, and we don't need the running balance. The manual entries resembled a report, so you naturally obtained a running balance. If you made an addition error, your books of account would not balance.

In contrast the "trial balance" process with computer data merely verifies that the grand total is zero, and therefore the subtotals can be relied upon. if it doesn't balance then one of the individual "double entry" transaction sets must have failed (been out of balance), and you then need a way of summing each "double entry" subset to find and review the one(s) that didn't add to zero. There may be 100,000 records all summing to zero. The running totals are of no importance at all, just the overall total.

 

[MarkK]

Do a running sum at presentation time, not at query time.

Eventually during presentation you are going to need to display every row, which will be a sequential iteration over the entire set. That is the simplest and most efficient time to calculate and present a running sum.

 

[ahmad_rmh]

The issue with a running sum is that it depends on the sort order of the records. If that changes the running sum needs to be re-evaluated/re-calculated. A report has a fixed order, so it can be calculated as each row is printed, and will not then change.

The underlying reason it's hard is that a set of records (a recordset) is a collection of individual records. Therefore for each record the next and previous records are not defined. Because there is no concept of a "next" record for a data set, there is just no such thing as a simple running sum of which you can keep track. So there is no formula for a running sum.

So although you could find an inefficient way to do it, it's best not to bother, and just consider, for example, the total of the set of records, rather than the partial total evaluated as you navigate through the records. That way, you work with what you have.

In the case of a collection of stock records, it ought to be enough that the entire collections sums to 12,500 widgets, and not bother about what the partial total was after each individual transaction that comprised the total of 12,500. What does it matter that the total WAS 12,520, and you just reduced that total by 20 with the last sale record. If you sorted it by transaction amount, rather than date, the running total declares differently, but the final total is still the same. Hence concentrate on the final total.

With a trial balance in a ledger, for instance, you just need to know the total for each account, and that the overall total of all the transactions is zero. When the ledger was maintained manually, you would have to sum the figures as you went, but we handle the records differently and more flexibly now, and we don't need the running balance. The manual entries resembled a report, so you naturally obtained a running balance. If you made an addition error, your books of account would not balance.

In contrast the "trial balance" process with computer data merely verifies that the grand total is zero, and therefore the subtotals can be relied upon. if it doesn't balance then one of the individual "double entry" transaction sets must have failed (been out of balance), and you then need a way of summing each "double entry" subset to find and review the one(s) that didn't add to zero. There may be 100,000 records all summing to zero. The running totals are of no importance at all, just the overall total.

1. All of the ledgers of accounts are based on running sum.
2. During the audit process the auditor requires ledger details of specific heads of receipts/expenses.
3. Do you think that all of the users of accounting software are acquainted with double entry concepts. All of the accounting software front ends are based on single entry concept of accounting system. It's the sotware developers who are managing the data at the back ends in double entry accounting system.
I think so it's enough answer and I don't want to give more explanation about that.

 

[gemma-the-husky]

^^^^
Sorry. I do not think you are correct.
The users of accounting systems may not understand the double entry process. They don't need to. The accountants need to, and the software designers need to , and the software WILL be operating a double entry system.

I was trying to explain that there is in general no real need for many/any processes to need to generate a running total in a query, and the "trial balance" was just an example.

 

[ahmad_rmh]

and what about the reporting process of general ledgers?
Any how, if you are not using running sum in query then you have required in reports but you could not avoid that process to do.

 

[gemma-the-husky]

Reporting is different, as already explained by others. When you run a report you can specify any column for which you need a running total. As the report is printed, the total accumulates naturally, within the report - not within the query. You get subtotals for each report group, an overall total, and running totals as well, if you want them. The report order does not necessarily follow the query record order. This works because a "report" is a distinct sequential process, unlike the query. After you obtain the printed report, you can't then change the order. To do that, you need another report. A query essentially uses an unordered bucket of records (the same as a table, so you can't get a running total in a table either). Even when we sort a query, it just selects each record from the bucket, but in the order of the required sort. You can re-sort of filter the collection. You can easily produce domain totals - a sum of all the records, a count, an average, and so on - but It still doesn't actually process the individual records to produce running totals.

 

[CJ_London]

All of the ledgers of accounts are based on running sum

no they are not, they are based on transactions linked to accounts they do not include a running sum. Do not confuse presentation with underlying data. If you want that, use Excel

 

[Pat Hartman]

The point of a running sum in manual bookkeeping is that you always only have to add two numbers. Much less prone to error than summing hundreds of numbers. Also, in a manual ledger, the rows never move. Once the transaction is logged on line 9443, it NEVER moves.

The only time I have ever had to actually care about a running sum was for a client who was renting warehouse space. During the month, cartons came in and cartons went out but the client was billed based on the average cartons per day. So, I had to sum the inventory transactions to get a balance for each day - i.e. a running sum. Then I averaged the total of cartons per day by the number of days in the month.

For reporting, people like to see a running balance like in a checkbook but a report is a sequential process so the technique is exactly the same as what you would do manually. You add two numbers together and move to the next row. As someone pointed out, the issue with queries is that the sort sequence can change on the fly and that possibly changes how the running sum is calculated. With a query, each row has to sum all the preceding rows (that is why you need the unique ID which isn't necessary when performing the same action in a report), it is not just adding a saved value to the current record.