DSUM, Running total problem

N

Noreen McHugh

Hi

I have used an example from the Northwind sample database to write a query
to show running totals for the current year, but I am hust getting an error
in my running total column. Can somebody hae a look at my SQL and possibly
give me some ideas about where I am going wrong


SELECT QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot], QueryTY.[PM
Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C], QueryTY.[LY Date],
QueryLY.[AM Read Tot], QueryLY.[PM Read Tot], QueryLY.[AM Cust C],
QueryLY.[PM Cust C], QueryLY.[Staff Food], QueryTY.County, QueryTY.Company,
QueryTY.[Fresh Express], QueryTY.Relish, [QueryTY.AM Read Tot]+[QueryTY.PM
Read Tot] AS [TY Read Total], [QueryLY.AM Read Tot]+[QueryLY.PM Read Tot] AS
[LY Read Total], [QueryTY.AM Cust C]+[QueryTY.PM Cust C] AS [TY CCount],
[QueryLY.AM Cust C]+[QueryLY.PM Cust C] AS [LY CCount], [TY Read Total]/[TY
CCount] AS [TY Ave S], [LY Read Total]/[LY CCount] AS [LY Ave S],
DatePart("yyyy",[QueryTY.Date]) AS AYear, DatePart("m",[QueryTY.Date]) AS
AMonth, DSum("TY Read Total","DatePart (''m', [QueryTY.Date] )<= " &
[AMonth] & " And DatePart( 'yyyy', [QueryTY.Date] ) =" & [AYear] & "") AS
RunTot
FROM QueryTY INNER JOIN QueryLY ON (QueryTY.StoreId = QueryLY.StoreId) AND
(QueryTY.[LY Date] = QueryLY.Date)
WHERE (((DatePart("yyyy",[QueryTY.Date]))>=2005))
GROUP BY QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot], QueryTY.[PM
Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C], QueryTY.[LY Date],
QueryLY.[AM Read Tot], QueryLY.[PM Read Tot], QueryLY.[AM Cust C],
QueryLY.[PM Cust C], QueryLY.[Staff Food], QueryTY.County, QueryTY.Company,
QueryTY.[Fresh Express], QueryTY.Relish, [QueryTY.AM Read Tot]+[QueryTY.PM
Read Tot], [QueryLY.AM Read Tot]+[QueryLY.PM Read Tot], [QueryTY.AM Cust
C]+[QueryTY.PM Cust C], [QueryLY.AM Cust C]+[QueryLY.PM Cust C], [TY Read
Total]/[TY CCount], [LY Read Total]/[LY CCount],
([QueryTY].[Date])>#1/1/2005#, DatePart("yyyy",[QueryTY.Date]),
DatePart("m",[QueryTY.Date])
HAVING (((QueryTY.Date)>#6/1/2005#) AND
((([QueryTY].[Date])>#1/1/2005#)<#12/31/2005#))
ORDER BY DatePart("yyyy",[QueryTY.Date]), DatePart("m",[QueryTY.Date]);




Regards


Noreen
 
T

Tom Ellison

Dear Noreen:

Could we do this on something a bit simpler? There's a lot of information
in your query that is probably not essential to your question, and there is
some information that is not revealed.

Does your running sum add up all the rows in your query, or does the sum
start over based on a certain set of columns. If so, which columns?

As you use no aggregate functions, why do you GROUP BY anything?

You need to know not to use this construction:

[QueryTY.Date]

This refers to a column whose name has a period in it. Almost certainly,
you need:

[QueryTY].[Date]

or

QueryTY.[Date]

You only need the square brackets when a column or table name includes a
space or other non-letter or non-digit character, or when it is a reserved
word, like [Date]. That's why [QueryTY.Date] must reference either a table
or a column, but not a table and a column. See?

Possibly this could be all you need to fix this. If not, please come back.

Tom Ellison


Noreen McHugh said:
Hi

I have used an example from the Northwind sample database to write a query
to show running totals for the current year, but I am hust getting an
error in my running total column. Can somebody hae a look at my SQL and
possibly give me some ideas about where I am going wrong


SELECT QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot], QueryTY.[PM
Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C], QueryTY.[LY Date],
QueryLY.[AM Read Tot], QueryLY.[PM Read Tot], QueryLY.[AM Cust C],
QueryLY.[PM Cust C], QueryLY.[Staff Food], QueryTY.County,
QueryTY.Company, QueryTY.[Fresh Express], QueryTY.Relish, [QueryTY.AM Read
Tot]+[QueryTY.PM Read Tot] AS [TY Read Total], [QueryLY.AM Read
Tot]+[QueryLY.PM Read Tot] AS [LY Read Total], [QueryTY.AM Cust
C]+[QueryTY.PM Cust C] AS [TY CCount], [QueryLY.AM Cust C]+[QueryLY.PM
Cust C] AS [LY CCount], [TY Read Total]/[TY CCount] AS [TY Ave S], [LY
Read Total]/[LY CCount] AS [LY Ave S], DatePart("yyyy",[QueryTY.Date]) AS
AYear, DatePart("m",[QueryTY.Date]) AS AMonth, DSum("TY Read
Total","DatePart (''m', [QueryTY.Date] )<= " & [AMonth] & " And
DatePart( 'yyyy', [QueryTY.Date] ) =" & [AYear] & "") AS RunTot
FROM QueryTY INNER JOIN QueryLY ON (QueryTY.StoreId = QueryLY.StoreId) AND
(QueryTY.[LY Date] = QueryLY.Date)
WHERE (((DatePart("yyyy",[QueryTY.Date]))>=2005))
GROUP BY QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot], QueryTY.[PM
Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C], QueryTY.[LY Date],
QueryLY.[AM Read Tot], QueryLY.[PM Read Tot], QueryLY.[AM Cust C],
QueryLY.[PM Cust C], QueryLY.[Staff Food], QueryTY.County,
QueryTY.Company, QueryTY.[Fresh Express], QueryTY.Relish, [QueryTY.AM Read
Tot]+[QueryTY.PM Read Tot], [QueryLY.AM Read Tot]+[QueryLY.PM Read Tot],
[QueryTY.AM Cust C]+[QueryTY.PM Cust C], [QueryLY.AM Cust C]+[QueryLY.PM
Cust C], [TY Read Total]/[TY CCount], [LY Read Total]/[LY CCount],
([QueryTY].[Date])>#1/1/2005#, DatePart("yyyy",[QueryTY.Date]),
DatePart("m",[QueryTY.Date])
HAVING (((QueryTY.Date)>#6/1/2005#) AND
((([QueryTY].[Date])>#1/1/2005#)<#12/31/2005#))
ORDER BY DatePart("yyyy",[QueryTY.Date]), DatePart("m",[QueryTY.Date]);




Regards


Noreen
Click to expand...
 
N

Noreen McHugh

Well I have adjusted to QueryTY.[Date] but it made no difference. My
running total is to add all read totals but start over for each year. So its
a yearly cumulative I need for the TY Read Total column.




Tom Ellison said:
Dear Noreen:

Could we do this on something a bit simpler? There's a lot of information
in your query that is probably not essential to your question, and there
is some information that is not revealed.

Does your running sum add up all the rows in your query, or does the sum
start over based on a certain set of columns. If so, which columns?

As you use no aggregate functions, why do you GROUP BY anything?

You need to know not to use this construction:

[QueryTY.Date]

This refers to a column whose name has a period in it. Almost certainly,
you need:

[QueryTY].[Date]

or

QueryTY.[Date]

You only need the square brackets when a column or table name includes a
space or other non-letter or non-digit character, or when it is a reserved
word, like [Date]. That's why [QueryTY.Date] must reference either a
table or a column, but not a table and a column. See?

Possibly this could be all you need to fix this. If not, please come
back.

Tom Ellison


Noreen McHugh said:
Hi

I have used an example from the Northwind sample database to write a
query to show running totals for the current year, but I am hust getting
an error in my running total column. Can somebody hae a look at my SQL
and possibly give me some ideas about where I am going wrong


SELECT QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot], QueryTY.[PM
Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C], QueryTY.[LY Date],
QueryLY.[AM Read Tot], QueryLY.[PM Read Tot], QueryLY.[AM Cust C],
QueryLY.[PM Cust C], QueryLY.[Staff Food], QueryTY.County,
QueryTY.Company, QueryTY.[Fresh Express], QueryTY.Relish, [QueryTY.AM
Read Tot]+[QueryTY.PM Read Tot] AS [TY Read Total], [QueryLY.AM Read
Tot]+[QueryLY.PM Read Tot] AS [LY Read Total], [QueryTY.AM Cust
C]+[QueryTY.PM Cust C] AS [TY CCount], [QueryLY.AM Cust C]+[QueryLY.PM
Cust C] AS [LY CCount], [TY Read Total]/[TY CCount] AS [TY Ave S], [LY
Read Total]/[LY CCount] AS [LY Ave S], DatePart("yyyy",[QueryTY.Date]) AS
AYear, DatePart("m",[QueryTY.Date]) AS AMonth, DSum("TY Read
Total","DatePart (''m', [QueryTY.Date] )<= " & [AMonth] & " And
DatePart( 'yyyy', [QueryTY.Date] ) =" & [AYear] & "") AS RunTot
FROM QueryTY INNER JOIN QueryLY ON (QueryTY.StoreId = QueryLY.StoreId)
AND (QueryTY.[LY Date] = QueryLY.Date)
WHERE (((DatePart("yyyy",[QueryTY.Date]))>=2005))
GROUP BY QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot],
QueryTY.[PM Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C],
QueryTY.[LY Date], QueryLY.[AM Read Tot], QueryLY.[PM Read Tot],
QueryLY.[AM Cust C], QueryLY.[PM Cust C], QueryLY.[Staff Food],
QueryTY.County, QueryTY.Company, QueryTY.[Fresh Express], QueryTY.Relish,
[QueryTY.AM Read Tot]+[QueryTY.PM Read Tot], [QueryLY.AM Read
Tot]+[QueryLY.PM Read Tot], [QueryTY.AM Cust C]+[QueryTY.PM Cust C],
[QueryLY.AM Cust C]+[QueryLY.PM Cust C], [TY Read Total]/[TY CCount], [LY
Read Total]/[LY CCount], ([QueryTY].[Date])>#1/1/2005#,
DatePart("yyyy",[QueryTY.Date]), DatePart("m",[QueryTY.Date])
HAVING (((QueryTY.Date)>#6/1/2005#) AND
((([QueryTY].[Date])>#1/1/2005#)<#12/31/2005#))
ORDER BY DatePart("yyyy",[QueryTY.Date]), DatePart("m",[QueryTY.Date]);




Regards


Noreen
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Noreen:

Where you now have:

DSum("TY Read
Total","DatePart (''m', [QueryTY.Date] )<= " & [AMonth] & " And
DatePart( 'yyyy', [QueryTY.Date] ) =" & [AYear] & "") AS RunTot

I suggest:

(SELECT SUM([TY Read Total])
FROM QueryTY Q1
WHERE Q1.[Date] <= QueryTY.[Date]
AND Year(Q1.[Date] = Year(QueryTy.[Date]))
AS RunTot

Hopefully you do not have two rows with the same [Date]. If you do, then
the running sum will add both simultaneously, not just one at a time in the
order they appear. That's because this order is not unique, but arbitrary.
For this reason, it may be useful to constrain the data in the underlying
table so this cannot happen, adding a unique index.

Many many note I do not use the "domain" functions, but use a subquery
instead. There are some reasons for this:

- the subquery is portable (because it is standard SQL)

- the subquery is more flexible toward future revisions

- the subquery is not really more difficult (once you get used to it)

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison


Noreen McHugh said:
Well I have adjusted to QueryTY.[Date] but it made no difference. My
running total is to add all read totals but start over for each year. So
its a yearly cumulative I need for the TY Read Total column.




Tom Ellison said:
Dear Noreen:

Could we do this on something a bit simpler? There's a lot of
information in your query that is probably not essential to your
question, and there is some information that is not revealed.

Does your running sum add up all the rows in your query, or does the sum
start over based on a certain set of columns. If so, which columns?

As you use no aggregate functions, why do you GROUP BY anything?

You need to know not to use this construction:

[QueryTY.Date]

This refers to a column whose name has a period in it. Almost certainly,
you need:

[QueryTY].[Date]

or

QueryTY.[Date]

You only need the square brackets when a column or table name includes a
space or other non-letter or non-digit character, or when it is a
reserved word, like [Date]. That's why [QueryTY.Date] must reference
either a table or a column, but not a table and a column. See?

Possibly this could be all you need to fix this. If not, please come
back.

Tom Ellison


Noreen McHugh said:
Hi

I have used an example from the Northwind sample database to write a
query to show running totals for the current year, but I am hust getting
an error in my running total column. Can somebody hae a look at my SQL
and possibly give me some ideas about where I am going wrong


SELECT QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot], QueryTY.[PM
Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C], QueryTY.[LY Date],
QueryLY.[AM Read Tot], QueryLY.[PM Read Tot], QueryLY.[AM Cust C],
QueryLY.[PM Cust C], QueryLY.[Staff Food], QueryTY.County,
QueryTY.Company, QueryTY.[Fresh Express], QueryTY.Relish, [QueryTY.AM
Read Tot]+[QueryTY.PM Read Tot] AS [TY Read Total], [QueryLY.AM Read
Tot]+[QueryLY.PM Read Tot] AS [LY Read Total], [QueryTY.AM Cust
C]+[QueryTY.PM Cust C] AS [TY CCount], [QueryLY.AM Cust C]+[QueryLY.PM
Cust C] AS [LY CCount], [TY Read Total]/[TY CCount] AS [TY Ave S], [LY
Read Total]/[LY CCount] AS [LY Ave S], DatePart("yyyy",[QueryTY.Date])
AS AYear, DatePart("m",[QueryTY.Date]) AS AMonth, DSum("TY Read
Total","DatePart (''m', [QueryTY.Date] )<= " & [AMonth] & " And
DatePart( 'yyyy', [QueryTY.Date] ) =" & [AYear] & "") AS RunTot
FROM QueryTY INNER JOIN QueryLY ON (QueryTY.StoreId = QueryLY.StoreId)
AND (QueryTY.[LY Date] = QueryLY.Date)
WHERE (((DatePart("yyyy",[QueryTY.Date]))>=2005))
GROUP BY QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot],
QueryTY.[PM Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C],
QueryTY.[LY Date], QueryLY.[AM Read Tot], QueryLY.[PM Read Tot],
QueryLY.[AM Cust C], QueryLY.[PM Cust C], QueryLY.[Staff Food],
QueryTY.County, QueryTY.Company, QueryTY.[Fresh Express],
QueryTY.Relish, [QueryTY.AM Read Tot]+[QueryTY.PM Read Tot], [QueryLY.AM
Read Tot]+[QueryLY.PM Read Tot], [QueryTY.AM Cust C]+[QueryTY.PM Cust
C], [QueryLY.AM Cust C]+[QueryLY.PM Cust C], [TY Read Total]/[TY
CCount], [LY Read Total]/[LY CCount], ([QueryTY].[Date])>#1/1/2005#,
DatePart("yyyy",[QueryTY.Date]), DatePart("m",[QueryTY.Date])
HAVING (((QueryTY.Date)>#6/1/2005#) AND
((([QueryTY].[Date])>#1/1/2005#)<#12/31/2005#))
ORDER BY DatePart("yyyy",[QueryTY.Date]), DatePart("m",[QueryTY.Date]);




Regards


Noreen
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

I was missing a paren:

(SELECT SUM([TY Read Total])
FROM QueryTY Q1
WHERE Q1.[Date] <= QueryTY.[Date]
AND Year(Q1.[Date]) = Year(QueryTy.[Date]))
AS RunTot

Tom Elison


Tom Ellison said:
Dear Noreen:

Where you now have:

DSum("TY Read
Total","DatePart (''m', [QueryTY.Date] )<= " & [AMonth] & " And
DatePart( 'yyyy', [QueryTY.Date] ) =" & [AYear] & "") AS RunTot

I suggest:

(SELECT SUM([TY Read Total])
FROM QueryTY Q1
WHERE Q1.[Date] <= QueryTY.[Date]
AND Year(Q1.[Date] = Year(QueryTy.[Date]))
AS RunTot

Hopefully you do not have two rows with the same [Date]. If you do, then
the running sum will add both simultaneously, not just one at a time in
the order they appear. That's because this order is not unique, but
arbitrary. For this reason, it may be useful to constrain the data in the
underlying table so this cannot happen, adding a unique index.

Many many note I do not use the "domain" functions, but use a subquery
instead. There are some reasons for this:

- the subquery is portable (because it is standard SQL)

- the subquery is more flexible toward future revisions

- the subquery is not really more difficult (once you get used to it)

Please let me know if this helped, and if I can be of any other
assistance.

Tom Ellison


Noreen McHugh said:
Well I have adjusted to QueryTY.[Date] but it made no difference. My
running total is to add all read totals but start over for each year. So
its a yearly cumulative I need for the TY Read Total column.




Tom Ellison said:
Dear Noreen:

Could we do this on something a bit simpler? There's a lot of
information in your query that is probably not essential to your
question, and there is some information that is not revealed.

Does your running sum add up all the rows in your query, or does the sum
start over based on a certain set of columns. If so, which columns?

As you use no aggregate functions, why do you GROUP BY anything?

You need to know not to use this construction:

[QueryTY.Date]

This refers to a column whose name has a period in it. Almost
certainly, you need:

[QueryTY].[Date]

or

QueryTY.[Date]

You only need the square brackets when a column or table name includes a
space or other non-letter or non-digit character, or when it is a
reserved word, like [Date]. That's why [QueryTY.Date] must reference
either a table or a column, but not a table and a column. See?

Possibly this could be all you need to fix this. If not, please come
back.

Tom Ellison


Hi

I have used an example from the Northwind sample database to write a
query to show running totals for the current year, but I am hust
getting an error in my running total column. Can somebody hae a look at
my SQL and possibly give me some ideas about where I am going wrong


SELECT QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot],
QueryTY.[PM Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C],
QueryTY.[LY Date], QueryLY.[AM Read Tot], QueryLY.[PM Read Tot],
QueryLY.[AM Cust C], QueryLY.[PM Cust C], QueryLY.[Staff Food],
QueryTY.County, QueryTY.Company, QueryTY.[Fresh Express],
QueryTY.Relish, [QueryTY.AM Read Tot]+[QueryTY.PM Read Tot] AS [TY Read
Total], [QueryLY.AM Read Tot]+[QueryLY.PM Read Tot] AS [LY Read Total],
[QueryTY.AM Cust C]+[QueryTY.PM Cust C] AS [TY CCount], [QueryLY.AM
Cust C]+[QueryLY.PM Cust C] AS [LY CCount], [TY Read Total]/[TY CCount]
AS [TY Ave S], [LY Read Total]/[LY CCount] AS [LY Ave S],
DatePart("yyyy",[QueryTY.Date]) AS AYear, DatePart("m",[QueryTY.Date])
AS AMonth, DSum("TY Read Total","DatePart (''m', [QueryTY.Date] )<= " &
[AMonth] & " And DatePart( 'yyyy', [QueryTY.Date] ) =" & [AYear] & "")
AS RunTot
FROM QueryTY INNER JOIN QueryLY ON (QueryTY.StoreId = QueryLY.StoreId)
AND (QueryTY.[LY Date] = QueryLY.Date)
WHERE (((DatePart("yyyy",[QueryTY.Date]))>=2005))
GROUP BY QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot],
QueryTY.[PM Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C],
QueryTY.[LY Date], QueryLY.[AM Read Tot], QueryLY.[PM Read Tot],
QueryLY.[AM Cust C], QueryLY.[PM Cust C], QueryLY.[Staff Food],
QueryTY.County, QueryTY.Company, QueryTY.[Fresh Express],
QueryTY.Relish, [QueryTY.AM Read Tot]+[QueryTY.PM Read Tot],
[QueryLY.AM Read Tot]+[QueryLY.PM Read Tot], [QueryTY.AM Cust
C]+[QueryTY.PM Cust C], [QueryLY.AM Cust C]+[QueryLY.PM Cust C], [TY
Read Total]/[TY CCount], [LY Read Total]/[LY CCount],
([QueryTY].[Date])>#1/1/2005#, DatePart("yyyy",[QueryTY.Date]),
DatePart("m",[QueryTY.Date])
HAVING (((QueryTY.Date)>#6/1/2005#) AND
((([QueryTY].[Date])>#1/1/2005#)<#12/31/2005#))
ORDER BY DatePart("yyyy",[QueryTY.Date]), DatePart("m",[QueryTY.Date]);




Regards


Noreen
Click to expand...
Click to expand...
Click to expand...
 
N

Noreen McHugh

Tom


I am still having difficulties with this. I think I have been trying to do
too much in one query.I will have to start again and try to make it simpler.
I have a table with the column for Read Totals, StoreID and date. I have one
read total for each store for each day. I want a running total column to
show the cululative read totals for each year for each store. So this
running total must reset at the beginning of each year. I presume there must
be a simple way to do this but I am unable figure it out.






Tom Ellison said:
I was missing a paren:

(SELECT SUM([TY Read Total])
FROM QueryTY Q1
WHERE Q1.[Date] <= QueryTY.[Date]
AND Year(Q1.[Date]) = Year(QueryTy.[Date]))
AS RunTot

Tom Elison


Tom Ellison said:
Dear Noreen:

Where you now have:

DSum("TY Read
Total","DatePart (''m', [QueryTY.Date] )<= " & [AMonth] & " And
DatePart( 'yyyy', [QueryTY.Date] ) =" & [AYear] & "") AS RunTot

I suggest:

(SELECT SUM([TY Read Total])
FROM QueryTY Q1
WHERE Q1.[Date] <= QueryTY.[Date]
AND Year(Q1.[Date] = Year(QueryTy.[Date]))
AS RunTot

Hopefully you do not have two rows with the same [Date]. If you do, then
the running sum will add both simultaneously, not just one at a time in
the order they appear. That's because this order is not unique, but
arbitrary. For this reason, it may be useful to constrain the data in the
underlying table so this cannot happen, adding a unique index.

Many many note I do not use the "domain" functions, but use a subquery
instead. There are some reasons for this:

- the subquery is portable (because it is standard SQL)

- the subquery is more flexible toward future revisions

- the subquery is not really more difficult (once you get used to it)

Please let me know if this helped, and if I can be of any other
assistance.

Tom Ellison


Noreen McHugh said:
Well I have adjusted to QueryTY.[Date] but it made no difference. My
running total is to add all read totals but start over for each year. So
its a yearly cumulative I need for the TY Read Total column.




Dear Noreen:

Could we do this on something a bit simpler? There's a lot of
information in your query that is probably not essential to your
question, and there is some information that is not revealed.

Does your running sum add up all the rows in your query, or does the
sum start over based on a certain set of columns. If so, which
columns?

As you use no aggregate functions, why do you GROUP BY anything?

You need to know not to use this construction:

[QueryTY.Date]

This refers to a column whose name has a period in it. Almost
certainly, you need:

[QueryTY].[Date]

or

QueryTY.[Date]

You only need the square brackets when a column or table name includes
a space or other non-letter or non-digit character, or when it is a
reserved word, like [Date]. That's why [QueryTY.Date] must reference
either a table or a column, but not a table and a column. See?

Possibly this could be all you need to fix this. If not, please come
back.

Tom Ellison


Hi

I have used an example from the Northwind sample database to write a
query to show running totals for the current year, but I am hust
getting an error in my running total column. Can somebody hae a look
at my SQL and possibly give me some ideas about where I am going wrong


SELECT QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot],
QueryTY.[PM Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C],
QueryTY.[LY Date], QueryLY.[AM Read Tot], QueryLY.[PM Read Tot],
QueryLY.[AM Cust C], QueryLY.[PM Cust C], QueryLY.[Staff Food],
QueryTY.County, QueryTY.Company, QueryTY.[Fresh Express],
QueryTY.Relish, [QueryTY.AM Read Tot]+[QueryTY.PM Read Tot] AS [TY
Read Total], [QueryLY.AM Read Tot]+[QueryLY.PM Read Tot] AS [LY Read
Total], [QueryTY.AM Cust C]+[QueryTY.PM Cust C] AS [TY CCount],
[QueryLY.AM Cust C]+[QueryLY.PM Cust C] AS [LY CCount], [TY Read
Total]/[TY CCount] AS [TY Ave S], [LY Read Total]/[LY CCount] AS [LY
Ave S], DatePart("yyyy",[QueryTY.Date]) AS AYear,
DatePart("m",[QueryTY.Date]) AS AMonth, DSum("TY Read Total","DatePart
(''m', [QueryTY.Date] )<= " & [AMonth] & " And DatePart( 'yyyy',
[QueryTY.Date] ) =" & [AYear] & "") AS RunTot
FROM QueryTY INNER JOIN QueryLY ON (QueryTY.StoreId = QueryLY.StoreId)
AND (QueryTY.[LY Date] = QueryLY.Date)
WHERE (((DatePart("yyyy",[QueryTY.Date]))>=2005))
GROUP BY QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot],
QueryTY.[PM Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C],
QueryTY.[LY Date], QueryLY.[AM Read Tot], QueryLY.[PM Read Tot],
QueryLY.[AM Cust C], QueryLY.[PM Cust C], QueryLY.[Staff Food],
QueryTY.County, QueryTY.Company, QueryTY.[Fresh Express],
QueryTY.Relish, [QueryTY.AM Read Tot]+[QueryTY.PM Read Tot],
[QueryLY.AM Read Tot]+[QueryLY.PM Read Tot], [QueryTY.AM Cust
C]+[QueryTY.PM Cust C], [QueryLY.AM Cust C]+[QueryLY.PM Cust C], [TY
Read Total]/[TY CCount], [LY Read Total]/[LY CCount],
([QueryTY].[Date])>#1/1/2005#, DatePart("yyyy",[QueryTY.Date]),
DatePart("m",[QueryTY.Date])
HAVING (((QueryTY.Date)>#6/1/2005#) AND
((([QueryTY].[Date])>#1/1/2005#)<#12/31/2005#))
ORDER BY DatePart("yyyy",[QueryTY.Date]),
DatePart("m",[QueryTY.Date]);




Regards


Noreen
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Noreen:

OK. Now I may have the information needed to help you.

You not only need to "reset" the running sum for each year, but also for
each store. Right?

I put something into the query fragment I gave you to reset the total by
year. I'll add the part for resetting the total by store.

(SELECT SUM([TY Read Total])
FROM QueryTY Q1
WHERE Q1.[Date] <= QueryTY.[Date]
AND Year(Q1.[Date]) = Year(QueryTY.[Date])
AND Q1.StoreID = QueryTY.StoreID)
AS RunTot

Is that better? Does it start the total over by year AND by store?

Tom Ellison


Noreen McHugh said:
Tom


I am still having difficulties with this. I think I have been trying to do
too much in one query.I will have to start again and try to make it
simpler. I have a table with the column for Read Totals, StoreID and date.
I have one read total for each store for each day. I want a running total
column to show the cululative read totals for each year for each store. So
this running total must reset at the beginning of each year. I presume
there must be a simple way to do this but I am unable figure it out.






Tom Ellison said:
I was missing a paren:

(SELECT SUM([TY Read Total])
FROM QueryTY Q1
WHERE Q1.[Date] <= QueryTY.[Date]
AND Year(Q1.[Date]) = Year(QueryTy.[Date]))
AS RunTot

Tom Elison


Tom Ellison said:
Dear Noreen:

Where you now have:

DSum("TY Read
Total","DatePart (''m', [QueryTY.Date] )<= " & [AMonth] & " And
DatePart( 'yyyy', [QueryTY.Date] ) =" & [AYear] & "") AS RunTot

I suggest:

(SELECT SUM([TY Read Total])
FROM QueryTY Q1
WHERE Q1.[Date] <= QueryTY.[Date]
AND Year(Q1.[Date] = Year(QueryTy.[Date]))
AS RunTot

Hopefully you do not have two rows with the same [Date]. If you do,
then the running sum will add both simultaneously, not just one at a
time in the order they appear. That's because this order is not unique,
but arbitrary. For this reason, it may be useful to constrain the data
in the underlying table so this cannot happen, adding a unique index.

Many many note I do not use the "domain" functions, but use a subquery
instead. There are some reasons for this:

- the subquery is portable (because it is standard SQL)

- the subquery is more flexible toward future revisions

- the subquery is not really more difficult (once you get used to it)

Please let me know if this helped, and if I can be of any other
assistance.

Tom Ellison


Well I have adjusted to QueryTY.[Date] but it made no difference. My
running total is to add all read totals but start over for each year.
So its a yearly cumulative I need for the TY Read Total column.




Dear Noreen:

Could we do this on something a bit simpler? There's a lot of
information in your query that is probably not essential to your
question, and there is some information that is not revealed.

Does your running sum add up all the rows in your query, or does the
sum start over based on a certain set of columns. If so, which
columns?

As you use no aggregate functions, why do you GROUP BY anything?

You need to know not to use this construction:

[QueryTY.Date]

This refers to a column whose name has a period in it. Almost
certainly, you need:

[QueryTY].[Date]

or

QueryTY.[Date]

You only need the square brackets when a column or table name includes
a space or other non-letter or non-digit character, or when it is a
reserved word, like [Date]. That's why [QueryTY.Date] must reference
either a table or a column, but not a table and a column. See?

Possibly this could be all you need to fix this. If not, please come
back.

Tom Ellison


Hi

I have used an example from the Northwind sample database to write a
query to show running totals for the current year, but I am hust
getting an error in my running total column. Can somebody hae a look
at my SQL and possibly give me some ideas about where I am going
wrong


SELECT QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot],
QueryTY.[PM Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C],
QueryTY.[LY Date], QueryLY.[AM Read Tot], QueryLY.[PM Read Tot],
QueryLY.[AM Cust C], QueryLY.[PM Cust C], QueryLY.[Staff Food],
QueryTY.County, QueryTY.Company, QueryTY.[Fresh Express],
QueryTY.Relish, [QueryTY.AM Read Tot]+[QueryTY.PM Read Tot] AS [TY
Read Total], [QueryLY.AM Read Tot]+[QueryLY.PM Read Tot] AS [LY Read
Total], [QueryTY.AM Cust C]+[QueryTY.PM Cust C] AS [TY CCount],
[QueryLY.AM Cust C]+[QueryLY.PM Cust C] AS [LY CCount], [TY Read
Total]/[TY CCount] AS [TY Ave S], [LY Read Total]/[LY CCount] AS [LY
Ave S], DatePart("yyyy",[QueryTY.Date]) AS AYear,
DatePart("m",[QueryTY.Date]) AS AMonth, DSum("TY Read
Total","DatePart (''m', [QueryTY.Date] )<= " & [AMonth] & " And
DatePart( 'yyyy', [QueryTY.Date] ) =" & [AYear] & "") AS RunTot
FROM QueryTY INNER JOIN QueryLY ON (QueryTY.StoreId =
QueryLY.StoreId) AND (QueryTY.[LY Date] = QueryLY.Date)
WHERE (((DatePart("yyyy",[QueryTY.Date]))>=2005))
GROUP BY QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot],
QueryTY.[PM Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C],
QueryTY.[LY Date], QueryLY.[AM Read Tot], QueryLY.[PM Read Tot],
QueryLY.[AM Cust C], QueryLY.[PM Cust C], QueryLY.[Staff Food],
QueryTY.County, QueryTY.Company, QueryTY.[Fresh Express],
QueryTY.Relish, [QueryTY.AM Read Tot]+[QueryTY.PM Read Tot],
[QueryLY.AM Read Tot]+[QueryLY.PM Read Tot], [QueryTY.AM Cust
C]+[QueryTY.PM Cust C], [QueryLY.AM Cust C]+[QueryLY.PM Cust C], [TY
Read Total]/[TY CCount], [LY Read Total]/[LY CCount],
([QueryTY].[Date])>#1/1/2005#, DatePart("yyyy",[QueryTY.Date]),
DatePart("m",[QueryTY.Date])
HAVING (((QueryTY.Date)>#6/1/2005#) AND
((([QueryTY].[Date])>#1/1/2005#)<#12/31/2005#))
ORDER BY DatePart("yyyy",[QueryTY.Date]),
DatePart("m",[QueryTY.Date]);




Regards


Noreen
Click to expand...
Click to expand...
Click to expand...
 
N

Noreen McHugh

yes, I need a separate running total for each store which starts over each
year


Tom Ellison said:
Dear Noreen:

OK. Now I may have the information needed to help you.

You not only need to "reset" the running sum for each year, but also for
each store. Right?

I put something into the query fragment I gave you to reset the total by
year. I'll add the part for resetting the total by store.

(SELECT SUM([TY Read Total])
FROM QueryTY Q1
WHERE Q1.[Date] <= QueryTY.[Date]
AND Year(Q1.[Date]) = Year(QueryTY.[Date])
AND Q1.StoreID = QueryTY.StoreID)
AS RunTot

Is that better? Does it start the total over by year AND by store?

Tom Ellison


Noreen McHugh said:
Tom


I am still having difficulties with this. I think I have been trying to
do too much in one query.I will have to start again and try to make it
simpler. I have a table with the column for Read Totals, StoreID and
date. I have one read total for each store for each day. I want a running
total column to show the cululative read totals for each year for each
store. So this running total must reset at the beginning of each year. I
presume there must be a simple way to do this but I am unable figure it
out.






Tom Ellison said:
I was missing a paren:

(SELECT SUM([TY Read Total])
FROM QueryTY Q1
WHERE Q1.[Date] <= QueryTY.[Date]
AND Year(Q1.[Date]) = Year(QueryTy.[Date]))
AS RunTot

Tom Elison


Dear Noreen:

Where you now have:

DSum("TY Read
Total","DatePart (''m', [QueryTY.Date] )<= " & [AMonth] & " And
DatePart( 'yyyy', [QueryTY.Date] ) =" & [AYear] & "") AS RunTot

I suggest:

(SELECT SUM([TY Read Total])
FROM QueryTY Q1
WHERE Q1.[Date] <= QueryTY.[Date]
AND Year(Q1.[Date] = Year(QueryTy.[Date]))
AS RunTot

Hopefully you do not have two rows with the same [Date]. If you do,
then the running sum will add both simultaneously, not just one at a
time in the order they appear. That's because this order is not
unique, but arbitrary. For this reason, it may be useful to constrain
the data in the underlying table so this cannot happen, adding a unique
index.

Many many note I do not use the "domain" functions, but use a subquery
instead. There are some reasons for this:

- the subquery is portable (because it is standard SQL)

- the subquery is more flexible toward future revisions

- the subquery is not really more difficult (once you get used to it)

Please let me know if this helped, and if I can be of any other
assistance.

Tom Ellison


Well I have adjusted to QueryTY.[Date] but it made no difference. My
running total is to add all read totals but start over for each year.
So its a yearly cumulative I need for the TY Read Total column.




Dear Noreen:

Could we do this on something a bit simpler? There's a lot of
information in your query that is probably not essential to your
question, and there is some information that is not revealed.

Does your running sum add up all the rows in your query, or does the
sum start over based on a certain set of columns. If so, which
columns?

As you use no aggregate functions, why do you GROUP BY anything?

You need to know not to use this construction:

[QueryTY.Date]

This refers to a column whose name has a period in it. Almost
certainly, you need:

[QueryTY].[Date]

or

QueryTY.[Date]

You only need the square brackets when a column or table name
includes a space or other non-letter or non-digit character, or when
it is a reserved word, like [Date]. That's why [QueryTY.Date] must
reference either a table or a column, but not a table and a column.
See?

Possibly this could be all you need to fix this. If not, please come
back.

Tom Ellison


Hi

I have used an example from the Northwind sample database to write a
query to show running totals for the current year, but I am hust
getting an error in my running total column. Can somebody hae a look
at my SQL and possibly give me some ideas about where I am going
wrong


SELECT QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot],
QueryTY.[PM Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C],
QueryTY.[LY Date], QueryLY.[AM Read Tot], QueryLY.[PM Read Tot],
QueryLY.[AM Cust C], QueryLY.[PM Cust C], QueryLY.[Staff Food],
QueryTY.County, QueryTY.Company, QueryTY.[Fresh Express],
QueryTY.Relish, [QueryTY.AM Read Tot]+[QueryTY.PM Read Tot] AS [TY
Read Total], [QueryLY.AM Read Tot]+[QueryLY.PM Read Tot] AS [LY Read
Total], [QueryTY.AM Cust C]+[QueryTY.PM Cust C] AS [TY CCount],
[QueryLY.AM Cust C]+[QueryLY.PM Cust C] AS [LY CCount], [TY Read
Total]/[TY CCount] AS [TY Ave S], [LY Read Total]/[LY CCount] AS [LY
Ave S], DatePart("yyyy",[QueryTY.Date]) AS AYear,
DatePart("m",[QueryTY.Date]) AS AMonth, DSum("TY Read
Total","DatePart (''m', [QueryTY.Date] )<= " & [AMonth] & " And
DatePart( 'yyyy', [QueryTY.Date] ) =" & [AYear] & "") AS RunTot
FROM QueryTY INNER JOIN QueryLY ON (QueryTY.StoreId =
QueryLY.StoreId) AND (QueryTY.[LY Date] = QueryLY.Date)
WHERE (((DatePart("yyyy",[QueryTY.Date]))>=2005))
GROUP BY QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot],
QueryTY.[PM Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C],
QueryTY.[LY Date], QueryLY.[AM Read Tot], QueryLY.[PM Read Tot],
QueryLY.[AM Cust C], QueryLY.[PM Cust C], QueryLY.[Staff Food],
QueryTY.County, QueryTY.Company, QueryTY.[Fresh Express],
QueryTY.Relish, [QueryTY.AM Read Tot]+[QueryTY.PM Read Tot],
[QueryLY.AM Read Tot]+[QueryLY.PM Read Tot], [QueryTY.AM Cust
C]+[QueryTY.PM Cust C], [QueryLY.AM Cust C]+[QueryLY.PM Cust C], [TY
Read Total]/[TY CCount], [LY Read Total]/[LY CCount],
([QueryTY].[Date])>#1/1/2005#, DatePart("yyyy",[QueryTY.Date]),
DatePart("m",[QueryTY.Date])
HAVING (((QueryTY.Date)>#6/1/2005#) AND
((([QueryTY].[Date])>#1/1/2005#)<#12/31/2005#))
ORDER BY DatePart("yyyy",[QueryTY.Date]),
DatePart("m",[QueryTY.Date]);




Regards


Noreen
Click to expand...
Click to expand...
Click to expand...
 
N

Noreen McHugh

Tom


Thats great, I am actually able to run the query now and am getting some
sort of figures. The totals are not right yet but hopefully will be with
some tweaking



Noreen


Tom Ellison said:
Dear Noreen:

OK. Now I may have the information needed to help you.

You not only need to "reset" the running sum for each year, but also for
each store. Right?

I put something into the query fragment I gave you to reset the total by
year. I'll add the part for resetting the total by store.

(SELECT SUM([TY Read Total])
FROM QueryTY Q1
WHERE Q1.[Date] <= QueryTY.[Date]
AND Year(Q1.[Date]) = Year(QueryTY.[Date])
AND Q1.StoreID = QueryTY.StoreID)
AS RunTot

Is that better? Does it start the total over by year AND by store?

Tom Ellison


Noreen McHugh said:
Tom


I am still having difficulties with this. I think I have been trying to
do too much in one query.I will have to start again and try to make it
simpler. I have a table with the column for Read Totals, StoreID and
date. I have one read total for each store for each day. I want a running
total column to show the cululative read totals for each year for each
store. So this running total must reset at the beginning of each year. I
presume there must be a simple way to do this but I am unable figure it
out.






Tom Ellison said:
I was missing a paren:

(SELECT SUM([TY Read Total])
FROM QueryTY Q1
WHERE Q1.[Date] <= QueryTY.[Date]
AND Year(Q1.[Date]) = Year(QueryTy.[Date]))
AS RunTot

Tom Elison


Dear Noreen:

Where you now have:

DSum("TY Read
Total","DatePart (''m', [QueryTY.Date] )<= " & [AMonth] & " And
DatePart( 'yyyy', [QueryTY.Date] ) =" & [AYear] & "") AS RunTot

I suggest:

(SELECT SUM([TY Read Total])
FROM QueryTY Q1
WHERE Q1.[Date] <= QueryTY.[Date]
AND Year(Q1.[Date] = Year(QueryTy.[Date]))
AS RunTot

Hopefully you do not have two rows with the same [Date]. If you do,
then the running sum will add both simultaneously, not just one at a
time in the order they appear. That's because this order is not
unique, but arbitrary. For this reason, it may be useful to constrain
the data in the underlying table so this cannot happen, adding a unique
index.

Many many note I do not use the "domain" functions, but use a subquery
instead. There are some reasons for this:

- the subquery is portable (because it is standard SQL)

- the subquery is more flexible toward future revisions

- the subquery is not really more difficult (once you get used to it)

Please let me know if this helped, and if I can be of any other
assistance.

Tom Ellison


Well I have adjusted to QueryTY.[Date] but it made no difference. My
running total is to add all read totals but start over for each year.
So its a yearly cumulative I need for the TY Read Total column.




Dear Noreen:

Could we do this on something a bit simpler? There's a lot of
information in your query that is probably not essential to your
question, and there is some information that is not revealed.

Does your running sum add up all the rows in your query, or does the
sum start over based on a certain set of columns. If so, which
columns?

As you use no aggregate functions, why do you GROUP BY anything?

You need to know not to use this construction:

[QueryTY.Date]

This refers to a column whose name has a period in it. Almost
certainly, you need:

[QueryTY].[Date]

or

QueryTY.[Date]

You only need the square brackets when a column or table name
includes a space or other non-letter or non-digit character, or when
it is a reserved word, like [Date]. That's why [QueryTY.Date] must
reference either a table or a column, but not a table and a column.
See?

Possibly this could be all you need to fix this. If not, please come
back.

Tom Ellison


Hi

I have used an example from the Northwind sample database to write a
query to show running totals for the current year, but I am hust
getting an error in my running total column. Can somebody hae a look
at my SQL and possibly give me some ideas about where I am going
wrong


SELECT QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot],
QueryTY.[PM Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C],
QueryTY.[LY Date], QueryLY.[AM Read Tot], QueryLY.[PM Read Tot],
QueryLY.[AM Cust C], QueryLY.[PM Cust C], QueryLY.[Staff Food],
QueryTY.County, QueryTY.Company, QueryTY.[Fresh Express],
QueryTY.Relish, [QueryTY.AM Read Tot]+[QueryTY.PM Read Tot] AS [TY
Read Total], [QueryLY.AM Read Tot]+[QueryLY.PM Read Tot] AS [LY Read
Total], [QueryTY.AM Cust C]+[QueryTY.PM Cust C] AS [TY CCount],
[QueryLY.AM Cust C]+[QueryLY.PM Cust C] AS [LY CCount], [TY Read
Total]/[TY CCount] AS [TY Ave S], [LY Read Total]/[LY CCount] AS [LY
Ave S], DatePart("yyyy",[QueryTY.Date]) AS AYear,
DatePart("m",[QueryTY.Date]) AS AMonth, DSum("TY Read
Total","DatePart (''m', [QueryTY.Date] )<= " & [AMonth] & " And
DatePart( 'yyyy', [QueryTY.Date] ) =" & [AYear] & "") AS RunTot
FROM QueryTY INNER JOIN QueryLY ON (QueryTY.StoreId =
QueryLY.StoreId) AND (QueryTY.[LY Date] = QueryLY.Date)
WHERE (((DatePart("yyyy",[QueryTY.Date]))>=2005))
GROUP BY QueryTY.Date, QueryTY.StoreId, QueryTY.[AM Read Tot],
QueryTY.[PM Read Tot], QueryTY.[AM Cust C], QueryTY.[PM Cust C],
QueryTY.[LY Date], QueryLY.[AM Read Tot], QueryLY.[PM Read Tot],
QueryLY.[AM Cust C], QueryLY.[PM Cust C], QueryLY.[Staff Food],
QueryTY.County, QueryTY.Company, QueryTY.[Fresh Express],
QueryTY.Relish, [QueryTY.AM Read Tot]+[QueryTY.PM Read Tot],
[QueryLY.AM Read Tot]+[QueryLY.PM Read Tot], [QueryTY.AM Cust
C]+[QueryTY.PM Cust C], [QueryLY.AM Cust C]+[QueryLY.PM Cust C], [TY
Read Total]/[TY CCount], [LY Read Total]/[LY CCount],
([QueryTY].[Date])>#1/1/2005#, DatePart("yyyy",[QueryTY.Date]),
DatePart("m",[QueryTY.Date])
HAVING (((QueryTY.Date)>#6/1/2005#) AND
((([QueryTY].[Date])>#1/1/2005#)<#12/31/2005#))
ORDER BY DatePart("yyyy",[QueryTY.Date]),
DatePart("m",[QueryTY.Date]);




Regards


Noreen
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with cumulative totals by date 1
Running total in query 2

Top