Running Sum within Query

  • Thread starter Thread starter KiowaPilot
  • Start date Start date
K

KiowaPilot

I am trying to create a Query to feed a Chart in Access. With the following
data in table "Rawdata" [Flt_Date] [HRS]
I have Daily activites narrowed down to monthly totals with the following
SQL. producing "Query1":
2009 - 1 - 12.5
2009 - 2 - 18.2
2009 - 3 - 14.6
I would now like to have a column that creates a running total specifically
for the chart.
2009 - 1 - 12.5 - 12.5
2009 - 2 - 18.2 - 30.7
2009 - 3 - 14.6 - 45.3

SELECT DatePart("yyyy",[Flt_Date]) AS AYear, DatePart("m",[Flt_Date]) AS
AMonth, Sum(Rawdata.HRS) AS SumOfHRS
FROM Rawdata
GROUP BY DatePart("yyyy",[Flt_Date]), DatePart("m",[Flt_Date])
ORDER BY DatePart("yyyy",[Flt_Date]);
 
You could do it in code, but not the query. Or, create a report, and use the
Running Sum property of the text box.
 
KiowaPilot

You could do it through embedding a SELECT query within the original SELECT
query as follows, restricting the records to a date (year & month) prior to
or equal to the year & month that's currently being processed.

SELECT Year([Flt_Date]) AS AYear, Month([Flt_Date]) AS AMonth,
Sum(Rawdata.HRS) AS SumOfHRS, (SELECT Sum(S1.HRS) AS RunningSumOfHRS FROM
Rawdata AS S1 WHERE Year(S1.Flt_Date)<Year(Rawdata.Flt_Date) OR
Year(S1.Flt_Date)=Year(Rawdata.Flt_Date) And
Month(S1.Flt_Date)<=Month(Rawdata.Flt_Date)) AS RunningTotal
FROM Rawdata
GROUP BY Year([Flt_Date]), Month([Flt_Date])
ORDER BY Year([Flt_Date]);

Hope this helps.

S.Clark said:
You could do it in code, but not the query. Or, create a report, and use the
Running Sum property of the text box.

KiowaPilot said:
I am trying to create a Query to feed a Chart in Access. With the following
data in table "Rawdata" [Flt_Date] [HRS]
I have Daily activites narrowed down to monthly totals with the following
SQL. producing "Query1":
2009 - 1 - 12.5
2009 - 2 - 18.2
2009 - 3 - 14.6
I would now like to have a column that creates a running total specifically
for the chart.
2009 - 1 - 12.5 - 12.5
2009 - 2 - 18.2 - 30.7
2009 - 3 - 14.6 - 45.3

SELECT DatePart("yyyy",[Flt_Date]) AS AYear, DatePart("m",[Flt_Date]) AS
AMonth, Sum(Rawdata.HRS) AS SumOfHRS
FROM Rawdata
GROUP BY DatePart("yyyy",[Flt_Date]), DatePart("m",[Flt_Date])
ORDER BY DatePart("yyyy",[Flt_Date]);
Click to expand...
Click to expand...
 
Try this --
SELECT DatePart("yyyy",[Flt_Date]) AS AYear, DatePart("m",[Flt_Date]) AS
AMonth, Rawdata.HRS, (SELECT Sum(XX.HRS) FROM Rawdata AS [XX] WHERE
DatePart("m",[XX.Flt_Date]) <= DatePart("m",[Rawdata.Flt_Date])) AS SumOfHRS
FROM Rawdata
GROUP BY DatePart("yyyy",[Flt_Date]), DatePart("m",[Flt_Date]), Rawdata.HRS
ORDER BY DatePart("yyyy",[Flt_Date]);
 
Absolutely Perfect Thank you very much.

Andrew Tapp said:
KiowaPilot

You could do it through embedding a SELECT query within the original SELECT
query as follows, restricting the records to a date (year & month) prior to
or equal to the year & month that's currently being processed.

SELECT Year([Flt_Date]) AS AYear, Month([Flt_Date]) AS AMonth,
Sum(Rawdata.HRS) AS SumOfHRS, (SELECT Sum(S1.HRS) AS RunningSumOfHRS FROM
Rawdata AS S1 WHERE Year(S1.Flt_Date)<Year(Rawdata.Flt_Date) OR
Year(S1.Flt_Date)=Year(Rawdata.Flt_Date) And
Month(S1.Flt_Date)<=Month(Rawdata.Flt_Date)) AS RunningTotal
FROM Rawdata
GROUP BY Year([Flt_Date]), Month([Flt_Date])
ORDER BY Year([Flt_Date]);

Hope this helps.

S.Clark said:
You could do it in code, but not the query. Or, create a report, and use the
Running Sum property of the text box.

KiowaPilot said:
I am trying to create a Query to feed a Chart in Access. With the following
data in table "Rawdata" [Flt_Date] [HRS]
I have Daily activites narrowed down to monthly totals with the following
SQL. producing "Query1":
2009 - 1 - 12.5
2009 - 2 - 18.2
2009 - 3 - 14.6
I would now like to have a column that creates a running total specifically
for the chart.
2009 - 1 - 12.5 - 12.5
2009 - 2 - 18.2 - 30.7
2009 - 3 - 14.6 - 45.3

SELECT DatePart("yyyy",[Flt_Date]) AS AYear, DatePart("m",[Flt_Date]) AS
AMonth, Sum(Rawdata.HRS) AS SumOfHRS
FROM Rawdata
GROUP BY DatePart("yyyy",[Flt_Date]), DatePart("m",[Flt_Date])
ORDER BY DatePart("yyyy",[Flt_Date]);
Click to expand...
Click to expand...
Click to expand...
 
Back
Top