Help with cumulative totals by date

[Noreen]

I need to add a column to my query for cumulative totals but I want the
total to reset at the end of each year. I am unsure how to do this so I will
copy in the SQL from my query. Hopefully someone can have a look at it and
point me in the right direction.TY Read Total is the field I want to cumulate.




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]
FROM QueryTY INNER JOIN QueryLY ON (QueryTY.StoreId = QueryLY.StoreId) AND
(QueryTY.[LY Date] = QueryLY.Date)
WHERE (((QueryTY.Date)>#6/1/2005#) AND
((([QueryTY].[Date])>#1/1/2005#)<#12/31/2005#))
ORDER BY QueryTY.Date;

 

[John Nurick]

Hi Noreen,

You need a subquery to tot up the cumulative total. Here's an example
for the Northwind sample database. It returns in each row the total
freight so far that year:

SELECT A.OrderID, A.OrderDate, A.Freight,
(SELECT SUM(B.Freight)
FROM Orders AS B
WHERE (Year(B.OrderDate) = Year(A.OrderDate))
AND (B.OrderID <= A.OrderID)
) AS CumuFreight
FROM Orders AS A
ORDER BY A.OrderID;

I need to add a column to my query for cumulative totals but I want the
total to reset at the end of each year. I am unsure how to do this so I will
copy in the SQL from my query. Hopefully someone can have a look at it and
point me in the right direction.TY Read Total is the field I want to cumulate.




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]
FROM QueryTY INNER JOIN QueryLY ON (QueryTY.StoreId = QueryLY.StoreId) AND
(QueryTY.[LY Date] = QueryLY.Date)
WHERE (((QueryTY.Date)>#6/1/2005#) AND
((([QueryTY].[Date])>#1/1/2005#)<#12/31/2005#))
ORDER BY QueryTY.Date;