Cumulative Total with a Curve Ball

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 18:29
Joined
Feb 5, 2019
Messages
358
Hi forum brains. I am after a little help on a cumulative running total query.

I want this for our financial year, which runs Aug - Jul. I have managed to find a working method but only for Jan - Dec (see link https://mskb.pkisolutions.com/kb/208714)

Would anyone have anything they have maybe done in the past that has a similar awkward financial year setup?

~Matt
 
I regularly used to do this for academic years: Sept to Aug
Specify the start and end dates and add a YYYYMM field for each record e.g. 202509, 202510 to ensure all data is displayed in the correct order
 
I regularly used to do this for academic years: Sept to Aug
Specify the start and end dates and add a YYYYMM field for each record e.g. 202509, 202510 to ensure all data is displayed in the correct order
Hi Colin,

I don't suppose you have an example at all please? I have been on this since yesterday afternoon and just cannot make it work.

I have a field that details the financial year/month start and year/month end, but when I try to do a running sum, I just cannot figure it out.

An example would help me figure out where I have gone wrong.

~Matt
 
There are many ways this can be done and would help if you showed what you actually tried together with details of relevant tables and fields.

I would use two queries - the first to get all the periods you want and the second to calculate the cumulative totals

For the first, you may already have a table of period ends, but if you don't and there are transactions for every period you might have a query something like this (assumes your trandate field does not include a time element)

qryPeriodEnds
SELECT DISTINCT dateadd("m",1,dateserial(year(trandate),month(trandate),1))-1 as PeriodEnd
FROM tblTrans
WHERE trandate between #2024-08-01# and #2025-07-31#

the second

qryYTD
SELECT PeriodEnd, sum(tranamount) as Cumulative
FROM qryPeriodEnds INNER JOIN tbTrans ON trandate<=periodend
WHERE trandate>=#2024-08-01#
GROUP BY PeriodEnd
ORDER BY PeriodEnd

The second query uses a non standard join ( trandate<=periodend) and cannot be displayed in the QBE. In the QBE, just use the = then go to sql view to add the <
 
Last edited:
Not easily available at the moment and I'm going out for the rest of the day.
Can you upload a cut down database with just the required tables(s) and your query attempts.
Change any sensitive data before uploading
 
I want this for our financial year, which runs Aug - Jul. I have managed to find a working method but only for Jan - Dec
Can it be as simple as adding 7 months to the date to get its fiscal year?
 
The attached file illustrates the use of some simple little functions which return the accounting year for any date in the format YYYY-YY, where the start date of the accounting year can be any date. By default the UK fiscal year, which starts on 5th April, is used, but all that's necessary to use any accounting year is the change the values of the arguments passed into the functions.

As an example, the following query would return all transactions in accounting years 2008-09 and 2009-10, with their start and end dates, and quarter per accounting year starting on 1st April:

SQL:
SELECT
    TransactionID,
    AcctYear (4, 1, TransactionDate) AS AccountingYear,
    AcctYearStart (4, 1, TransactionDate) AS AccountingYearStart,
    DATEADD("yyyy", 1, AcctYearStart (4, 1, TransactionDate)) -1 AS AccountingYearEnd,
    AcctYearQuarter (4, 1, TransactionDate) AS AccountingYearQuarter,
    TransactionDate,
    TransactionAmount
FROM
    Transactions
WHERE
    AcctYear (4, 1, TransactionDate) IN ("2008-09", "2009-10")
ORDER BY
    TransactionDate;

The above query could then be used as the basis of another query, such as that below, to return the balance per transaction per accounting year. Note how the primary key TransactionID is used as the tie breaker in the case of two or more transactions on the same day:

SQL:
SELECT
    T1.TransactionDate,
    T1.TransactionAmount,
    T1.AccountingYear,
    SUM(T2.TransactionAmount) AS Balance
FROM
    qryTransactions AS T1
    INNER JOIN qryTransactions AS T2 ON (
        T2.TransactionID <= T1.TransactionID
        OR T2.TransactionDate <> T1.TransactionDate
    )
    AND (T2.TransactionDate <= T1.TransactionDate)
    AND (T2.AccountingYear = T1.AccountingYear)
GROUP BY
    T1.TransactionID,
    T1.TransactionDate,
    T1.TransactionAmount,
    T1.AccountingYear
ORDER BY
    T1.TransactionDate DESC,
    T1.TransactionID DESC;
 

Attachments

Last edited:
@MattBaldry

Personally, I would avoid trying to store a cumulative/running total in a table. I don't see the point especially as changing the sort order will change the running total. Can you not find a way to use the overall total of a (data)set to produce what you want?
 
@MattBaldry

Personally, I would avoid trying to store a cumulative/running total in a table. I don't see the point especially as changing the sort order will change the running total. Can you not find a way to use the overall total of a (data)set to produce what you want?

Agreed. Not only pointless, but more importantly, storing the computed values leaves the table open to the risk of update anomalies. The most efficient way to compute balances is by a join of two instances of the table, as in the example I posted earlier. Other examples, returning updatable or non-updatable result tables can be found in the attached little demo file:
 

Attachments

I now have this working, kind of. I shall post the solution here later.

~Matt
 
Please see below for working code. This was done for me by a colleague using ChatGPT.

It updates a field in a table, rather than as a query, but it does work perfectly.

Code:
Sub CumulativeRunningTotalByMonthFY()
    Dim db          As DAO.Database
    Dim rs          As DAO.Recordset
    Dim currentFY   As String
    Dim currentMonth As String
    Dim runningSum  As Double
    Dim transDate   As Date
    Dim fy          As String
    Dim monthPeriod As String
    
    Set db = CurrentDb
    ' Sort by date to ensure cumulative logic works
    Set rs = db.OpenRecordset("SELECT * FROM Transactions ORDER BY TransDate", dbOpenDynaset)
    
    If rs.EOF Then
        MsgBox "No records found."
        Exit Sub
    End If
    
    runningSum = 0
    currentFY = ""
    currentMonth = ""
    
    rs.MoveFirst
    Do Until rs.EOF
        transDate = rs!TransDate
        
        ' --- Determine Financial Year (April–March) ---
        If Month(transDate) >= 4 Then
            fy = Year(transDate) & "-" & Right(Year(transDate) + 1, 2)
        Else
            fy = (Year(transDate) - 1) & "-" & Right(Year(transDate), 2)
        End If
        
        ' --- Determine Month Label ---
        monthPeriod = Format(transDate, "mmm yyyy")
        
        ' --- Reset running sum when FY changes ---
        If fy <> currentFY Then
            runningSum = 0
            currentFY = fy
        End If
        
        runningSum = runningSum + rs!Amount
        
        ' --- Update table ---
        rs.Edit
        rs!FY = fy
        rs!MonthPeriod = monthPeriod
        rs!RunningSum = runningSum
        rs.Update
        
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    MsgBox "Cumulative running total by month within financial year completed!"
End Sub

From this I have used a query on the table to get the MAX value in each month to then generate my line graph.

1760533681354.png


We have 3 full financial years and the current one displayed. The data is pulled from Sage, which is horribly slow, so having it in a local table works much better for what we need.

~Matt
 
FWIW, there is no need to initialise variables, if they have only just been dimmed.
They are already initialised as per type.
 
FWIW, there is no need to initialise variables, if they have only just been dimmed.
They are already initialised as per type.
This section?

Code:
    runningSum = 0
    currentFY = ""
    currentMonth = ""
 
Yes.
You would need to do so within a loop, if something changed, but not before you had populated the variables with values.
 
Yes.
You would need to do so within a loop, if something changed, but not before you had populated the variables with values.
Never trust AI :)

I'll remove it to clean it up on my database, but leave it there on the example as it is working anyway.

~Matt
 
Please see below for working code. This was done for me by a colleague using ChatGPT.

It updates a field in a table, rather than as a query, but it does work perfectly.

You can do all that with a single query, and you don't have to risk update anomalies by storing computed values in a column in a table. Using my AccountingYear.accdb demo again, and an accounting year starting 1st April:

SQL:
SELECT
    AcctYear (4, 1, T1.TransactionDate) AS AccountingYear,
    MONTH(T1.TransactionDate) AS AccountingMonth,
    SUM(T1.TransactionAmount) AS MonthlyTotal,
    (
        SELECT
            SUM(TransactionAmount)
        FROM
            Transactions AS T2
        WHERE
            AcctYear (4, 1, T2.TransactionDate) = AcctYear (4, 1, T1.TransactionDate)
            AND MONTH(T2.TransactionDate) <= MONTH(T1.TransactionDate)
    ) AS Balance
FROM
    Transactions AS T1
GROUP BY
    AcctYear (4, 1, T1.TransactionDate),
    MONTH(T1.TransactionDate);
 
One correction to above. The accounting month needs to be formatted as 'yyyy mm' rather the calling the Month function, to cater for the accounting year spanning the calendar year:

SQL:
SELECT
    AcctYear (4, 1, T1.TransactionDate) AS AccountingYear,
    FORMAT(T1.TransactionDate, "yyyy mm") AS AccountingMonth,
    SUM(T1.TransactionAmount) AS MonthlyTotal,
    (
        SELECT
            SUM(TransactionAmount)
        FROM
            Transactions AS T2
        WHERE
            AcctYear (4, 1, T2.TransactionDate) = AcctYear (4, 1, T1.TransactionDate)
            AND FORMAT(T2.TransactionDate, "yyyy mm") <= FORMAT(T1.TransactionDate, "yyyy mm")
    ) AS Balance
FROM
    Transactions AS T1
GROUP BY
    AcctYear (4, 1, T1.TransactionDate),
    FORMAT(T1.TransactionDate, "yyyy mm");
 
Last edited:
One correction to above. The accounting month needs to be formatted as 'yyyy mm' rather the calling the Month function, to cater for the accounting year spanning the calendar year:

SQL:
SELECT
    AcctYear (4, 1, T1.TransactionDate) AS AccountingYear,
    FORMAT(T1.TransactionDate, "yyyy mm") AS AccountingMonth,
    SUM(T1.TransactionAmount) AS MonthlyTotal,
    (
        SELECT
            SUM(TransactionAmount)
        FROM
            Transactions AS T2
        WHERE
            AcctYear (4, 1, T2.TransactionDate) = AcctYear (4, 1, T1.TransactionDate)
            AND FORMAT(T2.TransactionDate, "yyyy mm") <= FORMAT(T1.TransactionDate, "yyyy mm")
    ) AS Balance
FROM
    Transactions AS T1
GROUP BY
    AcctYear (4, 1, T1.TransactionDate),
    FORMAT(T1.TransactionDate, "yyyy mm");
Thanks Ken,

This works the same as the code ChatGPT offered, but with the data we have is taking 3 times as long to open. Most of the people using this are logging in remotely and have the patience of Dr Shipman when waiting for anything to load. Sage already slows everything down massively with the data connection. I am currently working on something else to automate importing Sage into separate SQL tables to make things quicker, so will save your solution for when I have had time for the next step.

Thank you for the code, it is perfect for what I need once I have sorted the Sage issues.

~Matt
 
Not easily available at the moment and I'm going out for the rest of the day.
Can you upload a cut down database with just the required tables(s) and your query attempts.
Change any sensitive data before uploading
Hi Colin,

Sorry to be a pain, but do you know if there is a way of displaying the data table on the modern charts? I have had a look on your in depth page and couldn't see anything. At the moment I have done it as a subform to give the effect of being the datatable, but was wondering if it was possible to just add it to the chart itself.

1760614974799.png


This does give the user a visual, but it would be better if I could just add the datatable to the chart itself.

~Matt
 
Unfortunately not. I did request that feature about a year ago but it wasn’t taken up.
I use the same solution with a subform as you.
 

Users who are viewing this thread

Back
Top Bottom