Rolling Totals for 6 month and 12 month

I need help!
I have Totals query that give me Year, MonthID, Month - Groupby. Then sum
ManhoursWorked, Recoardables, Lost Time.

I need to get rolling total for 6 and 12 Month. I have 3 tables tblYear,
tblMonth, tblSafetyDataConstruction. They are inner joined together
tblYear.Year to tblSafetyDataConstruction.Year and tblMonth.Month to
tblSafetyDataConstruction.Month.
Here is my SQL:

SELECT tblYear.Years, tblMonth.MonthID, tblMonth.Month,
Sum(tblSafetyDataConstruction.ManhoursWorked) AS SumOfManhoursWorked,
Sum(tblSafetyDataConstruction.Recordables) AS SumOfRecordables,
Sum(tblSafetyDataConstruction.LostTime) AS SumOfLostTime
FROM tblYear INNER JOIN (tblMonth INNER JOIN tblSafetyDataConstruction ON
tblMonth.Month = tblSafetyDataConstruction.Month) ON tblYear.Years =
tblSafetyDataConstruction.Year
GROUP BY tblYear.Years, tblMonth.MonthID, tblMonth.Month;
I believe I need a subquery not sure.
Thank you in advance for help!!

--
Coach K
"Knowledge is Power"

Try this --
SELECT YYMM.Year & YYMM.Month AS Accounting_Month,
Sum(IIf(YYMM.Year & YYMM.Month Between
Format(DateAdd("m",-1,Date()),"yyyymm") AND
Format(DateAdd("m",-12,Date()),"yyyymm"),[ManhoursWorked],0)) AS
12_Month_ManhoursWorked, Sum(IIf(YYMM.Year & YYMM.Month Between
Format(DateAdd("m",-1,Date()),"yyyymm") AND
Format(DateAdd("m",-12,Date()),"yyyymm"),[Recordables],0)) AS
12_Month_Recordables, Sum(IIf(YYMM.Year & YYMM.Month Between
Format(DateAdd("m",-1,Date()),"yyyymm") AND
Format(DateAdd("m",-12,Date()),"yyyymm"),[LostTime],0)) AS 12_Month_LostTime,
Sum(IIf(YYMM.Year & YYMM.Month Between
Format(DateAdd("m",-1,Date()),"yyyymm") AND
Format(DateAdd("m",-6,Date()),"yyyymm"),[ManhoursWorked],0)) AS
6_Month_ManhoursWorked, Sum(IIf(YYMM.Year & YYMM.Month Between
Format(DateAdd("m",-1,Date()),"yyyymm") AND
Format(DateAdd("m",-6,Date()),"yyyymm"),[Recordables],0)) AS
6_Month_Recordables, Sum(IIf(YYMM.Year & YYMM.Month Between
Format(DateAdd("m",-1,Date()),"yyyymm") AND
Format(DateAdd("m",-6,Date()),"yyyymm"),[LostTime],0)) AS 6_Month_LostTime
FROM tblSafetyDataConstruction AS YYMM
GROUP BY YYMM.Year & YYMM.Month;


"Coach K" wrote:

I need help!
I have Totals query that give me Year, MonthID, Month - Groupby. Then sum
ManhoursWorked, Recoardables, Lost Time.

I need to get rolling total for 6 and 12 Month. I have 3 tables tblYear,
tblMonth, tblSafetyDataConstruction. They are inner joined together
tblYear.Year to tblSafetyDataConstruction.Year and tblMonth.Month to
tblSafetyDataConstruction.Month.
Here is my SQL:

SELECT tblYear.Years, tblMonth.MonthID, tblMonth.Month,
Sum(tblSafetyDataConstruction.ManhoursWorked) AS SumOfManhoursWorked,
Sum(tblSafetyDataConstruction.Recordables) AS SumOfRecordables,
Sum(tblSafetyDataConstruction.LostTime) AS SumOfLostTime
FROM tblYear INNER JOIN (tblMonth INNER JOIN tblSafetyDataConstruction ON
tblMonth.Month = tblSafetyDataConstruction.Month) ON tblYear.Years =
tblSafetyDataConstruction.Year
GROUP BY tblYear.Years, tblMonth.MonthID, tblMonth.Month;
I believe I need a subquery not sure.
Thank you in advance for help!!

--
Coach K
"Knowledge is Power"

Thank you Karl!!
But i forgot to explain that the Recordables are mupilty by 200000 then
divide by the manhoursworked
I.E. =((3*200000)/619818)
+(600000/619818)
=.96 RIR (Recordable Incident Rate)

This equation give me a incident rate for the month. Then I need the rolling
monthly total for 6 and 12. I have the query that total for each and year.

That is query is:
SELECT qryCSCharting.Year, qryCSCharting.Month,
qryCSCharting.SumOfManhoursWorked, qryCSCharting.SumOfRecordables,
[SumofRecordables]*200000/[sumofManhoursWorked] AS RIR
FROM qryCSCharting
ORDER BY qryCSCharting.Year, qryCSCharting.MonthID;

In addition, I am using this query to create the query above:

SELECT tblSafetyDataConstruction.Year, tblMonth.MonthID, tblMonth.Month,
Sum(tblSafetyDataConstruction.ManhoursWorked) AS SumOfManhoursWorked,
Sum(tblSafetyDataConstruction.Recordables) AS SumOfRecordables
FROM tblMonth INNER JOIN tblSafetyDataConstruction ON tblMonth.Month =
tblSafetyDataConstruction.Month
GROUP BY tblSafetyDataConstruction.Year, tblMonth.MonthID, tblMonth.Month;
Thank you for your help!
--
Coach K
"Knowledge is Power"


"KARL DEWEY" wrote:

Try this --
SELECT YYMM.Year & YYMM.Month AS Accounting_Month,
Sum(IIf(YYMM.Year & YYMM.Month Between
Format(DateAdd("m",-1,Date()),"yyyymm") AND
Format(DateAdd("m",-12,Date()),"yyyymm"),[ManhoursWorked],0)) AS
12_Month_ManhoursWorked, Sum(IIf(YYMM.Year & YYMM.Month Between
Format(DateAdd("m",-1,Date()),"yyyymm") AND
Format(DateAdd("m",-12,Date()),"yyyymm"),[Recordables],0)) AS
12_Month_Recordables, Sum(IIf(YYMM.Year & YYMM.Month Between
Format(DateAdd("m",-1,Date()),"yyyymm") AND
Format(DateAdd("m",-12,Date()),"yyyymm"),[LostTime],0)) AS 12_Month_LostTime,
Sum(IIf(YYMM.Year & YYMM.Month Between
Format(DateAdd("m",-1,Date()),"yyyymm") AND
Format(DateAdd("m",-6,Date()),"yyyymm"),[ManhoursWorked],0)) AS
6_Month_ManhoursWorked, Sum(IIf(YYMM.Year & YYMM.Month Between
Format(DateAdd("m",-1,Date()),"yyyymm") AND
Format(DateAdd("m",-6,Date()),"yyyymm"),[Recordables],0)) AS
6_Month_Recordables, Sum(IIf(YYMM.Year & YYMM.Month Between
Format(DateAdd("m",-1,Date()),"yyyymm") AND
Format(DateAdd("m",-6,Date()),"yyyymm"),[LostTime],0)) AS 6_Month_LostTime
FROM tblSafetyDataConstruction AS YYMM
GROUP BY YYMM.Year & YYMM.Month;


"Coach K" wrote:

I need help!
I have Totals query that give me Year, MonthID, Month - Groupby. Then sum
ManhoursWorked, Recoardables, Lost Time.

I need to get rolling total for 6 and 12 Month. I have 3 tables tblYear,
tblMonth, tblSafetyDataConstruction. They are inner joined together
tblYear.Year to tblSafetyDataConstruction.Year and tblMonth.Month to
tblSafetyDataConstruction.Month.
Here is my SQL:

SELECT tblYear.Years, tblMonth.MonthID, tblMonth.Month,
Sum(tblSafetyDataConstruction.ManhoursWorked) AS SumOfManhoursWorked,
Sum(tblSafetyDataConstruction.Recordables) AS SumOfRecordables,
Sum(tblSafetyDataConstruction.LostTime) AS SumOfLostTime
FROM tblYear INNER JOIN (tblMonth INNER JOIN tblSafetyDataConstruction ON
tblMonth.Month = tblSafetyDataConstruction.Month) ON tblYear.Years =
tblSafetyDataConstruction.Year
GROUP BY tblYear.Years, tblMonth.MonthID, tblMonth.Month;
I believe I need a subquery not sure.
Thank you in advance for help!!

--
Coach K
"Knowledge is Power"