Running Sum In Query

[Drand]

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]

use this Criteria:

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

 

[CJ_London]

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]

 

[Drand]

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

Thank you so much for this. The data does have multiple years so this works perfectly for each year.

Cheers

 

[Pat Hartman]

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.

 

[CJ_London]

which solution is incorrect?

 

[Drand]

which solution is incorrect?

Yours worked for me

 

[Pat Hartman]

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]

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]

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]

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]

Clarification is important since as you can see, it does change the formula Thank you.