Running Sum resetting each year

[boesch]

Trying to calculate a YTD running sum by Month where the cumulative amount resets the next year as below.

AYear Amonth SumOfFreight RunTot FDate
1995 1 $2,798.59 $2,798.59 Jan
1995 2 $1,675.06 $4,473.65 Feb
1995 3 $2,165.37 $6,639.02 Mar
1995 4 $1,661.66 $8,300.68 Apr
1995 5 $3,166.25 $11,466.93 May
1995 6 $3,461.40 $14,928.33 Jun
1995 7 $1,852.65 $16,780.98 Jul
1995 8 $2,458.72 $20,527.88 Aug
1995 9 $3,078.27 $25,003.32 Sep
1995 10 $3,237.05 $29,363.85 Oct
1995 11 $3,934.70 $34,708.77 Nov
1995 12 $2,019.68 $38,990.68 Dec
1996 1 $3,757.96 $3,757.96 Jan
1996 2 $5,395.28 $9,153.24 Feb
1996 3 $4,341.10 $13,494.34 Mar
1996 4 $5,379.02 $18,873.36 Apr

Here's my practice SQL (in NorthWinds.mdb

SELECT DatePart("yyyy",[OrderDate]) AS AYear, DatePart("m",[OrderDate]) AS Amonth, Sum(Orders.Freight) AS SumOfFreight, Val(DSum("Freight","Orders","DatePart('m',[OrderDate])<=" & [Amonth] & " And DatePart('yyyy',[OrderDate])<=" & [Ayear] & " ")) AS RunTot, Format([OrderDate],"mmm") AS FDate
FROM Orders
WHERE (((DatePart("yyyy",[OrderDate])) Between 1995 And 1996))
GROUP BY DatePart("yyyy",[OrderDate]), DatePart("m",[OrderDate]), Format([OrderDate],"mmm")
ORDER BY DatePart("yyyy",[OrderDate]), DatePart("m",[OrderDate]), Format([OrderDate],"mmm");

Here's what I get:
AYear Amonth SumOfFreight RunTot FDate
1995 1 $2,798.59 $2,798.59 Jan
1995 2 $1,675.06 $4,473.65 Feb
1995 3 $2,165.37 $6,639.02 Mar
1995 4 $1,661.66 $8,300.68 Apr
1995 5 $3,166.25 $11,466.93 May
1995 6 $3,461.40 $14,928.33 Jun
1995 7 $1,852.65 $16,780.98 Jul
1995 8 $2,458.72 $20,527.88 Aug
1995 9 $3,078.27 $25,003.32 Sep
1995 10 $3,237.05 $29,363.85 Oct
1995 11 $3,934.70 $34,708.77 Nov
1995 12 $2,019.68 $38,990.68 Dec
1996 1 $3,757.96 $6,556.55 Jan
1996 2 $5,395.28 $13,626.89 Feb
1996 3 $4,341.10 $20,133.36 Mar
1996 4 $5,379.02 $27,174.04 Apr
1996 5 $6,481.29 $36,821.58 May
1996 6 $597.36 $40,880.34 Jun

Anyone have an idea for correcting this problem?

Thanks in advance,

Barb

 

[pbaldy]

In the DSum, try changing the year criteria to = rather than <=.

 

[boesch]

Perfect
Just one little character can make a world of difference. Thanks for your help!

 

[Cosmos75]

Just a suggestion, you may want to reconsider using DSum() and use a SELECT subquery instead. It should be much faster than using DSum().

See the following article - Running (or moving) Calculations (Sum/Total, Count, Average)