Running sum on Query (1 Viewer)

helmerr

Registered User.
Local time
Today, 05:46
Joined
Nov 27, 2013
Messages
233
I was wondering if anyone could point me in the right direction:
I've added two new queries (based off one original query) in order to get different totals. The last step is to get a running total from certain fields.

Surely I'm doing this the hard way but the general hierarchy is:
qryReportSumAirlineTransactions - 1st query which obtains previous record values to sum current and previous totals for fuel issued and received.
qryReportSumAirlineTransactionSubTotals - 2nd query (based on 1st query) which is used to (SUM new and prev issued) and (SUM new and prev received).
qryReportSumAirlineTransactionTotals - 3rd query (based on 2nd query) used to obtain a Total: Sum([SubtotalReceived]+[SubtotalIssued]).

Now, I need a a way (in a query) to perform:
Running Sum of PrevReceived
Running Sum of NewReceived
Running Sum of NewIssued

Attached are my sample queries and related tables. You can use the being/end date of 10/07/20 for testing.
Sidenote, I've noticed when I backup or copy these queries sometimes it takes forever or appears to hang; not sure whats up w/ that.
Any help is appreciated!
 

Attachments

  • Database17.accdb
    1.7 MB · Views: 36

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:46
Joined
Feb 28, 2001
Messages
19,387
The question will first be, why? If you were planning to make a report with those running sums, there are ways to do them inside the report such that your query doesn't have to be so complex. Is there a reason you want the query to have all of these running sums?
 

helmerr

Registered User.
Local time
Today, 05:46
Joined
Nov 27, 2013
Messages
233
The question will first be, why? If you were planning to make a report with those running sums, there are ways to do them inside the report such that your query doesn't have to be so complex. Is there a reason you want the query to have all of these running sums?
I AM able to get running totals in the report, but the report requiring the running totals is a sub-report. So, when I reference the Running Sum control on the sub-report from the main report like =[rptSum2AirlineTransactions].[Report].[txtRunSumIssued]), the value is only updated when the report is viewed layout or report view; when viewed in print preview or printed, the value is not updated. Rather, it displays the first value instead of the running total.

I also forgot, I've tried using the function as described here also but I kept receiving an error "too few parameters." and points to this line in the function (I'm likely using the wrong things in my field for this.)
Set rst = db.OpenRecordset(qryName, dbOpenDynaset)

Sample of this version attached; reference qryReportSumAirlineTransactionTotals
 

Attachments

  • Database17-function.accdb
    1.7 MB · Views: 46

kentgorrell

New member
Local time
Today, 10:46
Joined
Dec 5, 2020
Messages
6
SQL:
SELECT TransactionID
, TransactionDate
, DSum("GallonsIssued","tblAirlineTransactions","TransactionID <= " & [tblAirlineTransactions].[TransactionID]) AS IssuedRunningSum
FROM tblAirlineTransactions
ORDER BY TransactionID;

This Simple example shows you how to get a running sum.
It assumes your Order By is on TransactionID but I note your IDs are in the reverse order compared to Dates.
you can paste into the sql view of a query to try it out

You would then need to tweak for filters and sorting.
and add columns for Received etc

this simple query may very well prove to be slow with a lot of records.
but at least it shows the simple way to get a running sum without resorting to methods that mess with your mind like recursion

You may even find a temp table could be your answer.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:46
Joined
Feb 19, 2013
Messages
12,926
A subquery will be faster, but the fastest way requires the use of a non standard join (which can only be done in sql)

Examples based on kentgorrel's suggestion

subquery
Code:
SELECT TransactionID
, TransactionDate
, (SELECT Sum(GallonsIssued) FROM tblAirlineTransactions T WHERE TransactionID <= [tblAirlineTransactions].[TransactionID]) AS IssuedRunningSum
FROM tblAirlineTransactions
ORDER BY TransactionID;

non standard join
Code:
SELECT A.TransactionID
, A.TransactionDate
, Sum(B.GallonsIssued) AS IssuedRunningSum
FROM tblAirlineTransactions A INNER JOIN  tblAirlineTransactions B ON B.TransactionID<=A.TransactionID
ORDER BY A.TransactionID;

there is a little cheat you can do to create the query using the query window. Create as 'normal' with a standard join, then go into the sql view and change the = to <=. Note the reversal of B.TransactionID<=A.TransactionID. An alternative without swapping these over would be A.TransactionID>B.TransactionID
 

kentgorrell

New member
Local time
Today, 10:46
Joined
Dec 5, 2020
Messages
6
I was trying to keep it simple.

Using a non standard join is often faster but it gets a little sporty and your example doesn't fly.
in fact I'd been trying to do something similar and couldn't get it to work either.
but your example got me closer.

Finally figured it out
It needs a Group By. The right Group By.
We are aggregating B.GallonsIssued but we group by A.TransactionID to get the right answer

Code:
SELECT A.TransactionID
, Sum(B.GallonsIssued) AS IssuedRunningSum
FROM tblAirlineTransactions AS A
    INNER JOIN tblAirlineTransactions B
    ON B.TransactionID <= A.TransactionID
GROUP BY A.TransactionID
ORDER BY A.TransactionID;

I've left the by order by as explicit on TransactionID because the result will be wrong if you try to group (First) on a column that is not unique and/or not used in the join. so if you wanted it sorted on Date then you would need to have a Date AND Time to make it unique. Or better still have transactions entered in chronological order.

Ahh, collaboration is a great thing, thanks CJ.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:46
Joined
Feb 19, 2013
Messages
12,926
oops, missed the group by, my bad:mad: but was freetyped
 

Users who are viewing this thread

Top Bottom