running sum (multiple years)

[turtle]

I need my running sum to work for more than one year. It works great
but then starts over when the year starts over. My example here is
straight from the Northwind database. I want a runningg sum of the
freight but for some reason it does a running sum for each year.
Anybody know how I can get it to keep running no matter the year.
thanks!

SELECT DatePart("yyyy" ,[orderdate]) AS ayear, DatePart("m",[orderdate])
AS amonth, Sum(Orders.Frei ght) AS SumOfFreight,
Format(DSum("Fr eight","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");

[John Winterbottom]

"turtle" <ko****@vistaco ntrols.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...

I need my running sum to work for more than one year. It works great
but then starts over when the year starts over. My example here is
straight from the Northwind database. I want a runningg sum of the
freight but for some reason it does a running sum for each year.
Anybody know how I can get it to keep running no matter the year.
thanks!

SELECT DatePart("yyyy" ,[orderdate]) AS ayear, DatePart("m",[orderdate])
AS amonth, Sum(Orders.Frei ght) AS SumOfFreight,
Format(DSum("Fr eight","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");

Grouping or sorting on a calculated column is bad for performance because
the query optimizer cannot make use of an index on the orderdate column if
one exists. Instead make a calendar table to hold all the months you are
ever likely to need, then join to this table in your query.

This code snippet will build and populate the calendar table.

'-------------------------------------------------------
Sub FillMonths()

Dim iMonth As Integer
Dim iYear As Long
Dim iDate As Date
Dim strSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo HandleErr

Set dbs = CurrentDb()
strSQL = "create table months(monthSta rt datetime not null " _
& "constraint PK_monthStart primary key, " _
& "monthEnd datetime not null constraint IX_monthEnd unique)"
dbs.Execute strSQL

' fill the month table with months from 1990-Jan-01 to 2020-Dec-31
Set rst = dbs.OpenRecords et("months", dbOpenTable, dbAppendOnly)
For iYear = 1990 To 2020
For iMonth = 1 To 12
iDate = CDate(iMonth & "/1/" & iYear) ' US format
rst.AddNew
rst("monthStart ") = iDate
rst("monthEnd") = DateAdd("m", 1, iDate) - 1
rst.Update
Next iMonth
Next iYear
ExitHere:
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
On Error GoTo 0
Exit Sub

HandleErr:
MsgBox (Err.Descriptio n)
Resume ExitHere

End Sub
'------------------------------------------

Then you query becomes something like this:

select format$(m.month Start,"mmm-yyyy") as month,
(
select sum(o2.Freight)
from orders as o2
where o2.OrderDate >= m.monthStart
and o2.OrderDate < m.monthEnd
) as SumOfFreight,
(
select sum(o2.Freight)
from orders as o2
where o2.OrderDate < m.monthEnd
) as RunTot
from months as m
order by m.monthStart

[jimfortune]

I created an Orders table:

OrderID OrderDate Freight
1 1/5/99 $25.00
2 2/24/00 $15.50
3 3/17/01 $22.00
4 12/17/01 $33.44
5 4/5/02 $222.00
6 5/5/03 $333.00
7 6/6/04 $444.00
8 7/7/04 $3.60

I then pasted in the same query and got:

ayear amonth SumOfFreight RunTot fdate
1999 1 $25.00 $0,025.00 Jan
2000 2 $15.50 $0,040.50 Feb
2001 3 $22.00 $0,062.50 Mar
2001 12 $33.44 $0,095.94 Dec
2002 4 $222.00 $0,284.50 Apr
2003 5 $333.00 $0,627.50 May
2004 6 $444.00 $1,061.50 Jun
2004 7 $3.60 $1,065.10 Jul

So the fifth result adds $222.00 to $62.50 ignoring month 12 because
it's past month 4. The basic idea is correct except that their logic
for comparing dates is wacky.

I created the following function:

Public Function CompareDates(dt 1 As Date, dt2 As Date) As Integer
If Year(dt1) < Year(dt2) Then
CompareDates = -1
Exit Function
End If
If Year(dt2) < Year(dt1) Then
CompareDates = 1
Exit Function
End If
If Month(dt1) < Month(dt2) Then
CompareDates = -1
Exit Function
End If
If Month(dt2) < Month(dt1) Then
CompareDates = 1
Exit Function
End If
If Day(dt1) < Day(dt2) Then
CompareDates = -1
Exit Function
End If
If Day(dt2) > Day(dt1) Then
CompareDates = 1
Else
CompareDates = 0
End If
End Function

qryFixed:
SELECT DatePart("yyyy" ,[orderdate]) AS ayear, DatePart("m",[orderdate])
AS amonth, Sum(Orders.Frei ght) AS SumOfFreight,
Format(DSum("Fr eight","Orders" ,"CompareDat es([orderdate], DateSerial("
& [ayear] & "," & [amonth] & " + 1, 0)) < 0" & ""),"$0,000.00" ) AS
RunTot, Format([orderdate],"mmm") AS fdate
FROM Orders
GROUP BY Orders.OrderDat e, Format([orderdate],"mmm"),
DatePart("yyyy" ,[orderdate]), DatePart("m",[orderdate])
ORDER BY DatePart("yyyy" ,[orderdate]), DatePart("m",[orderdate]),
Format([orderdate],"mmm");

returns a different RunTot:
RunTot
$0,025.00
$0,040.50
$0,062.50
$0,095.94
$0,317.94
$0,650.94
$1,094.94
$1,098.54

A DSum in a query is not much different than a query inside a query.

James A. Fortune

[jimfortune]

John Winterbottom wrote:

select format$(m.month Start,"mmm-yyyy") as month,
(
select sum(o2.Freight)
from orders as o2
where o2.OrderDate >= m.monthStart
and o2.OrderDate < m.monthEnd
) as SumOfFreight,
(
select sum(o2.Freight)
from orders as o2
where o2.OrderDate < m.monthEnd
) as RunTot
from months as m
order by m.monthStart

If you need any kind of speed at all use John's approach. I posted
another way to get the table of months a while back but it used a query
that needed a further make table query to obtain the kind of speed he's
talking about.

James A. Fortune

[turtle]

Jim,
That worked great for the northwinds database, when i try to get it to
work for my database I get an error.

My sql is:
SELECT DatePart("yyyy" ,[minofdate]) AS ayear, DatePart("m",[minofdate])
AS amonth,
Sum(graphev.50% compev) AS sumof50%compev,
Format(DSum("[50%compev]","graphev","Co mpareDates([minofdate],
DateSerial("& [ayear] & "," & [amonth] & " + 1, 0)) < 0" &
""),"$0,000.00" ) AS RunTot, Format([minofdate],"mmm") AS fdate
FROM graphev;

I get an error that says: Syntax error in query expression
'Sum(graphev.50 %compev)'

[Martha]

> I get an error that says: Syntax error in query expression

'Sum(graphev.50 %compev)'

Since your field name includes a non-alphabetic character (the percent
sign), you always have to enclose it in square brackets. In general,
however, it's not a terribly good idea to use symbols in a field name.
(Well, I guess underscores and hyphens are ok.)

--
HTH,
Martha
(don't google to email)

[turtle]

I changed my query to take out the % sign. Here is my new query:

SELECT DatePart("yyyy" ,[minofdate]) AS ayear, DatePart("m",[minofdate])
AS amonth, sum(evgraph.50c ompev) AS sumof50compev,
Format(DSum("50 compev","graphe v","CompareDate s([minofdate],
DateSerial("& [ayear] & "," & [amonth] & " + 1, 0)) < 0" &
""),"$0,000.00" ) AS RunTot, Format([minofdate],"mmm") AS fdate
FROM graphev;
Now I get "Syntax error (missing operator) in query expression
'sum(evgraph.50 compev)'.
If i bracket [50compev] i get "you tried to execute a query that does
not include the specified expression 'datepart("yyyy ,[minofdate])' as
part of an aggregate function.

[jimfortune]

turtle wrote:

I changed my query to take out the % sign. Here is my new query:

SELECT DatePart("yyyy" ,[minofdate]) AS ayear, DatePart("m",[minofdate]) AS amonth, sum(evgraph.50c ompev) AS sumof50compev,
Format(DSum("50 compev","graphe v","CompareDate s([minofdate],
DateSerial("& [ayear] & "," & [amonth] & " + 1, 0)) < 0" &
""),"$0,000.00" ) AS RunTot, Format([minofdate],"mmm") AS fdate
FROM graphev;
Now I get "Syntax error (missing operator) in query expression
'sum(evgraph.50 compev)'.
If i bracket [50compev] i get "you tried to execute a query that does
not include the specified expression 'datepart("yyyy ,[minofdate])' as
part of an aggregate function.

You can change CompareDates to:

Public Function CompareDates(dt 1 As Date, dt2 As Date) As Integer
If dt1 < dt2 Then
CompareDates = -1
ElseIf dt1 > dt2 Then
CompareDates = 1
Else
CompareDates = 0
End If
End Function

Or just use "[orderdate] < DateSerial("& [ayear] & "," & [amonth] & " +
1, 0)" instead of CompareDates(([orderdate], DateSerial("
& [ayear] & "," & [amonth] & " + 1, 0)) < 0"

The query I used to get a list of months is in:

http://groups-beta.google.com/group/...31790ec0622af9

Convert qryDisplayMonth s to a Make Table query if performance is an
issue. Still, take a good look at what John wrote. It's pretty nice.
I haven't looked at the latest problem yet.

James A. Fortune

[jimfortune]

turtle wrote:

I changed my query to take out the % sign. Here is my new query:

SELECT DatePart("yyyy" ,[minofdate]) AS ayear, DatePart("m",[minofdate]) AS amonth, sum(evgraph.50c ompev) AS sumof50compev,
Format(DSum("50 compev","graphe v","CompareDate s([minofdate],
DateSerial("& [ayear] & "," & [amonth] & " + 1, 0)) < 0" &
""),"$0,000.00" ) AS RunTot, Format([minofdate],"mmm") AS fdate
FROM graphev;
Now I get "Syntax error (missing operator) in query expression
'sum(evgraph.50 compev)'.
If i bracket [50compev] i get "you tried to execute a query that does
not include the specified expression 'datepart("yyyy ,[minofdate])' as
part of an aggregate function.

The 50compev is summed over the month of those minofdate's. If that's
what you want then you need to isolate each month with the group by.
Month alone isn't enough so the year of minofdate had to be included as
well. Perhaps explain what you are really trying to do. Also, the SQL
might be a lot simpler given that explanation.

James A. Fortune

[turtle]

Jim,
I think your last solution helped me!!! thanks.

Basically I am creating an Earned Value database so I need to know the
earned value for each month per Job. In the month the job starts the
earned value becomes 50% of the budget. When the job is completed the
Earned Value gets the other 50% of the budget. I want to create a
graph (hence the running sum) to show Earned Value. I think it is
working right now. Now I need to do the same thing with Budget and
Actuals and show all on one graph. I do believe that I will need to
make the months table and go from there.
thanks for your help!

Keith