Rolling Totals for 6 month and 12 month

C

Coach K

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!!
 
K

KARL DEWEY

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;
 
C

Coach K

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 said:
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 said:
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!!
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

Cumulative Totals in a query 2
Rolling 12 month 9
12 month rolling average by location 1
monthly totals 0
Avg. 1-month, 3-month, 6-month & 12-month Stock Returns 3
Sum of Numbers of a Rolling 6 month Period range 0
Duplicates - Query Joining 1
12 Month Running Total 3

Top