Cumulative Total with a Curve Ball

[MattBaldry]

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

 

[isladogs]

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

 

[MattBaldry]

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

 

[CJ_London]

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 <

 

[isladogs]

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

 

[sonic8]

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?

 

[Ken Sheridan]

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:

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:

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;