dsum issue

[turtle]

http://support.microsoft.com/default...b;en-us;208714

in the KB article above it gives a detailed explanation of how to do a
running sum in a query. However in this article they only do the
running sum for 1 year. When I run this on multiple years the running
sum starts over for each year. Can anyone tell me how to run this over
multiple years.

thanks,
KO

[Wayne Morgan]

I just tried the example they have and I show that the running sum is from
the start of the recordset. It isn't starting over each year. However, I was
able to make it start over each year by replacing

[OrderDate])<=" & [AYear] & "")

in #6 with

[OrderDate])=" & [AYear] & "")

Also, to go over multiple years, just remove the part in #8. Even with that
criteria in there, it limited what year was being shown, but the sum was
still over all of the years unless you changed <= to =.

--
Wayne Morgan
MS Access MVP
"turtle" <ko****@vistacontrols.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

http://support.microsoft.com/default...b;en-us;208714

in the KB article above it gives a detailed explanation of how to do a
running sum in a query. However in this article they only do the
running sum for 1 year. When I run this on multiple years the running
sum starts over for each year. Can anyone tell me how to run this over
multiple years.

thanks,
KO

[turtle]

Wayne,
When I run the query my runtot: column starts over for each year.
Here is the data that I get. I have no idea why I would get something
different from you. Can anyone else try to run it.

ayear amonth SumOfFreight RunTot fdate
1996 7 $1,288.18 1288.18 Jul
1996 8 $1,397.17 2685.35 Aug
1996 9 $1,123.48 3808.83 Sep
1996 10 $1,520.59 5329.42 Oct
1996 11 $2,151.86 7481.28 Nov
1996 12 $2,798.59 10279.87Dec
1997 1 $2,238.98 2238.98 Jan
1997 2 $1,601.45 3840.43 Feb
1997 3 $1,888.81 5729.24 Mar
1997 4 $2,939.10 8668.34 Apr
1997 5 $3,461.40 12129.74May
1997 6 $1,852.65 13982.39Jun
1997 7 $2,458.72 17729.29Jul
1997 8 $3,078.27 22204.73Aug
1997 9 $3,237.05 26565.26Sep
1997 10 $3,945.53 32031.38Oct
1997 11 $2,008.85 36192.09Nov
1997 12 $3,757.96 42748.64Dec
1998 1 $5,463.44 7702.42 Jan
1998 2 $4,272.94 13576.81Feb
1998 3 $5,379.02 20844.64Mar
1998 4 $6,393.57 30177.31Apr
1998 5 $685.08 34323.79May

[Wayne Morgan]

Will you post the SQL of your query. You've added one more column than is in
the example.

--
Wayne Morgan
MS Access MVP
"turtle" <ko****@vistacontrols.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Wayne,
When I run the query my runtot: column starts over for each year.
Here is the data that I get. I have no idea why I would get something
different from you. Can anyone else try to run it.

ayear amonth SumOfFreight RunTot fdate
1996 7 $1,288.18 1288.18 Jul
1996 8 $1,397.17 2685.35 Aug
1996 9 $1,123.48 3808.83 Sep
1996 10 $1,520.59 5329.42 Oct
1996 11 $2,151.86 7481.28 Nov
1996 12 $2,798.59 10279.87Dec
1997 1 $2,238.98 2238.98 Jan
1997 2 $1,601.45 3840.43 Feb
1997 3 $1,888.81 5729.24 Mar
1997 4 $2,939.10 8668.34 Apr
1997 5 $3,461.40 12129.74May
1997 6 $1,852.65 13982.39Jun
1997 7 $2,458.72 17729.29Jul
1997 8 $3,078.27 22204.73Aug
1997 9 $3,237.05 26565.26Sep
1997 10 $3,945.53 32031.38Oct
1997 11 $2,008.85 36192.09Nov
1997 12 $3,757.96 42748.64Dec
1998 1 $5,463.44 7702.42 Jan
1998 2 $4,272.94 13576.81Feb
1998 3 $5,379.02 20844.64Mar
1998 4 $6,393.57 30177.31Apr
1998 5 $685.08 34323.79May

[turtle]

Sorry if this double posts.... i added the freight column to see what
it was calculating. It doesn't work with it in or out of the query.

SELECT DatePart("yyyy",[orderdate]) AS ayear, DatePart("m",[orderdate])
AS amonth, Sum(Orders.Freight) AS SumOfFreight,
Format(DSum("Freight","Orders","DatePart ('yyyy', [OrderDate])<=" &
[Ayear] & " and DatePart('m', [OrderDate])<=" & [Amonth] &
""),"$0,000.00") AS RunTot, Format([orderdate],"mmm") AS fdate
FROM Orders
GROUP BY DatePart("yyyy",[orderdate]), DatePart("m",[orderdate]),
Format([orderdate],"mmm")
ORDER BY DatePart("yyyy",[orderdate]), DatePart("m",[orderdate]),
Format([orderdate],"mmm");

[turtle]

Wayne,
The extra field is just the freight field.... I was checking to see if
it was adding.

SELECT DatePart("yyyy",[orderdate]) AS ayear, DatePart("m",[orderdate])
AS amonth, Sum(Orders.Freight) AS SumOfFreight,
Format(DSum("Freight","Orders","DatePart ('yyyy', [OrderDate])<=" &
[Ayear] & " and DatePart('m', [OrderDate])<=" & [Amonth] &
""),"$0,000.00") AS RunTot, Format([orderdate],"mmm") AS fdate
FROM Orders
GROUP BY DatePart("yyyy",[orderdate]), DatePart("m",[orderdate]),
Format([orderdate],"mmm")
ORDER BY DatePart("yyyy",[orderdate]), DatePart("m",[orderdate]),
Format([orderdate],"mmm");

[Wayne Morgan]

I copied and pasted your SQL into a new query and ran it. This is what I got.

ayear amonth SumOfFreight RunTot fdate
1996 1 $6,098.00 $6,098.00 Jan
1997 1 $205.00 $6,303.00 Jan
1997 2 $5,555.00 $11,858.00 Feb
I manually added the items in the table. The values depicted are correct. Have you applied the most current Office and Jet service packs?

--
Wayne Morgan
MS Access MVP
"turtle" <ko****@vistacontrols.com> wrote in message news:11**********************@z14g2000cwz.googlegr oups.com...

Sorry if this double posts.... i added the freight column to see what
it was calculating. It doesn't work with it in or out of the query.

SELECT DatePart("yyyy",[orderdate]) AS ayear, DatePart("m",[orderdate])
AS amonth, Sum(Orders.Freight) AS SumOfFreight,
Format(DSum("Freight","Orders","DatePart ('yyyy', [OrderDate])<=" &
[Ayear] & " and DatePart('m', [OrderDate])<=" & [Amonth] &
""),"$0,000.00") AS RunTot, Format([orderdate],"mmm") AS fdate
FROM Orders
GROUP BY DatePart("yyyy",[orderdate]), DatePart("m",[orderdate]),
Format([orderdate],"mmm")
ORDER BY DatePart("yyyy",[orderdate]), DatePart("m",[orderdate]),
Format([orderdate],"mmm");

[turtle]

I am using Mircrosoft Access 2002 (10.4302.4219) SP-2
and msjet40.dll

Is that right?

[Wayne Morgan]

Office 2002 is on SP3 and Jet 4 is at SP8. The version number of msjet40.dll
in SP8 is 4.0.8015.0. To get this number, right click on msjet40.dll,
choose Properties, and go to the Version tab.

--
Wayne Morgan
MS Access MVP
"turtle" <ko****@vistacontrols.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I am using Mircrosoft Access 2002 (10.4302.4219) SP-2
and msjet40.dll

Is that right?