Cumulative Total with a Curve Ball (1 Viewer)

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 18:16
Joined
Feb 5, 2019
Messages
350
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
 

Users who are viewing this thread

Back
Top Bottom