Running Sum to Start Over for Different Groups

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've been following the discussions on Running Sums in a Query. I've successfully created a SQL statement that gives me the running sum over the entire query, however I can't get the Running Sum to start over for different groups. Here's my SQL text

SELECT [qsel Sales Detail].Key, [qsel Sales Detail].Salon_ID, [qsel Sales Detail].Fiscal_Year, [qsel Sales Detail].Fiscal_Period, [qsel Sales Detail].SumOfNet_Sales, Sum(Temp.SumOfNet_Sales) AS RunningSu
FROM [qsel Sales Detail], [qsel Sales Detail] AS Tem
WHERE ((([qsel Sales Detail].Key)>=[temp].[key])
GROUP BY [qsel Sales Detail].Key, [qsel Sales Detail].Salon_ID, [qsel Sales Detail].Fiscal_Year, [qsel Sales Detail].Fiscal_Period, [qsel Sales Detail].SumOfNet_Sale
ORDER BY [qsel Sales Detail].Salon_ID, [qsel Sales Detail].Fiscal_Year

The key is a unique field made up by concatenating Salon_ID, Fiscal_Year, and Fiscal_Period
I need my Running Sum to start over with each change in Fiscal_Year AND Salon_ID. Any help would be appreciated!
 
You might try a query whose SQL looks something like this:

SELECT
[qsel Sales Detail].Key,
[qsel Sales Detail].Salon_ID,
[qsel Sales Detail].Fiscal_Year,
[qsel Sales Detail].Fiscal_Period,
[qsel Sales Detail].SumOfNet_Sales,
Sum(Temp.SumOfNet_Sales) AS RunningSum
FROM
[qsel Sales Detail]
INNER JOIN
[qsel Sales Detail] AS Temp
ON
[qsel Sales Detail].Salon_ID = Temp.Salon_ID
AND
[qsel Sales Detail].Fiscal_Year = Temp.Fiscal_Year
WHERE
Temp.Fiscal_Period <= [qsel Sales Detail].Fiscal_Period
GROUP BY
[qsel Sales Detail].Key,
[qsel Sales Detail].Salon_ID,
[qsel Sales Detail].Fiscal_Year,
[qsel Sales Detail].Fiscal_Period,
[qsel Sales Detail].SumOfNet_Sales
ORDER BY
[qsel Sales Detail].Salon_ID,
[qsel Sales Detail].Fiscal_Year,
[qsel Sales Detail].Fiscal_Period

Another approach might be something like this:

SELECT
[qsel Sales Detail].Key,
[qsel Sales Detail].Salon_ID,
[qsel Sales Detail].Fiscal_Year,
[qsel Sales Detail].Fiscal_Period,
[qsel Sales Detail].SumOfNet_Sales,
(SELECT
Sum(Temp.SumOfNet_Sales)
FROM
[qsel Sales Detail] AS Temp
WHERE
Temp.Salon_ID = [qsel Sales Detail].Salon_ID
AND
Temp.Fiscal_Year = [qsel Sales Detail].Fiscal_Year
AND
Temp.Fiscal_Period <= [qsel Sales Detail].Fiscal_Period) AS RunningSum
FROM
[qsel Sales Detail]
ORDER BY
[qsel Sales Detail].Salon_ID,
[qsel Sales Detail].Fiscal_Year,
[qsel Sales Detail].Fiscal_Period



Kirk P said:
I've been following the discussions on Running Sums in a Query. I've
Click to expand...
successfully created a SQL statement that gives me the running sum over the
entire query, however I can't get the Running Sum to start over for
different groups. Here's my SQL text:
SELECT [qsel Sales Detail].Key, [qsel Sales Detail].Salon_ID, [qsel Sales
Click to expand...
Detail].Fiscal_Year, [qsel Sales Detail].Fiscal_Period, [qsel Sales
Detail].SumOfNet_Sales, Sum(Temp.SumOfNet_Sales) AS RunningSum
FROM [qsel Sales Detail], [qsel Sales Detail] AS Temp
WHERE ((([qsel Sales Detail].Key)>=[temp].[key]))
GROUP BY [qsel Sales Detail].Key, [qsel Sales Detail].Salon_ID, [qsel
Click to expand...
Sales Detail].Fiscal_Year, [qsel Sales Detail].Fiscal_Period, [qsel Sales
Detail].SumOfNet_Sales
ORDER BY [qsel Sales Detail].Salon_ID, [qsel Sales Detail].Fiscal_Year;

The key is a unique field made up by concatenating Salon_ID, Fiscal_Year, and Fiscal_Period.
I need my Running Sum to start over with each change in Fiscal_Year AND
Click to expand...
Salon_ID. Any help would be appreciated!
 
Back
Top