Running Sum In Query (1 Viewer)

Drand

Registered User.
Local time
Today, 11:54
Joined
Jun 8, 2019
Messages
179
Hi

I am trying to create a running sum in a query based on both months and years where both fields are integers.

My expression is "RunTot: DSum("TotalSales","QrySalesbyMonthandYear","Month<=" & [month] And "Year<=" & [Year])".

This seems to work when I only use the month criteria but when I add the year criteria it then sums all lines in the query as the running sum.

Is there something wrong with the syntax of this expression?

Many thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:54
Joined
May 7, 2009
Messages
19,169
use this Criteria:

"[Year] & Format$([Month],'00') <= '" & [Year] & Format$([Month], "00") & "'"
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:54
Joined
Feb 19, 2013
Messages
16,553
your criteria will be generating a syntax error if what you have posted is what you actually have

"Month<=" & [month] And "Year<=" & [Year]

should be

"Month<=" & [month] & " And Year<=" & [Year]

however if your data has multiple years you will be summing previous years as well. so perhaps

"Month<=" & [month] & " And Year=" & [Year]
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:54
Joined
Feb 19, 2002
Messages
42,981
Sorry, that solution is incorrect.

You need to use arne's suggestion to concatenate the two fields, YearMonth. Check your logic with November 2020 and Jan 2021. 2020 is less than 2021 but 11 is not less than 01. However 202011 IS less than 202101. Note that year MUST be first in this concatenation.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:54
Joined
Feb 19, 2002
Messages
42,981
What dates did you use to "test"? The separate field method ONLY works within a single year to get YTD figures.

So
"Month<=" & [month] & " And Year=" & [Year] -- would work ONLY for YTD.
And
"Month<=" & [month] & " And Year <=" & [Year] -- only works incidentally.
 

Drand

Registered User.
Local time
Today, 11:54
Joined
Jun 8, 2019
Messages
179
What dates did you use to "test"? The separate field method ONLY works within a single year to get YTD figures.

So
"Month<=" & [month] & " And Year=" & [Year] -- would work ONLY for YTD.
And
"Month<=" & [month] & " And Year <=" & [Year] -- only works incidentally.
I used "Month<=" & [month] & " And Year=" & [Year] but modified it to
"MonthNum<=" & [monthNum] & " And Year=" & [Year])
where MonthNum is the number of the months 1-12 sorted in ascending order.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:54
Joined
Feb 19, 2013
Messages
16,553
you might want to change your year field to yearnum - year is a reserved word, a function that returns the year of a date.

I suspect Pat's point is if your year doesn't start 1st Jan as it might for a P&L account, or you are looking for a true running sum (where Jan would include the previous years figures, such as a balance sheet) , you would need a different formula
 

Drand

Registered User.
Local time
Today, 11:54
Joined
Jun 8, 2019
Messages
179
you might want to change your year field to yearnum - year is a reserved word, a function that returns the year of a date.

I suspect Pat's point is if your year doesn't start 1st Jan as it might for a P&L account, or you are looking for a true running sum (where Jan would include the previous years figures, such as a balance sheet) , you would need a different formula
Agreed but I am working on calendar year from 1/1 to 31/12.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:54
Joined
Feb 19, 2002
Messages
42,981
Clarification is important since as you can see, it does change the formula:) Thank you.
 

Users who are viewing this thread

Top Bottom