If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
rolling 3 months
You guys are wonderful - this works like a gem! thank you !
"John Spencer" wrote: Try the following. Note the changes in the subquery where I specified TEMP as the table name. You had SELECT tblQA_RollingMonths.Task , tblQA_RollingMonths.EmployeeID , Count(tblQA_RollingMonths.LoggedMonthYear) AS CountMonths , Min(tblQA_RollingMonths.LoggedMonthYear) AS PeriodBegin , Max(tblQA_RollingMonths.LoggedMonthYear) AS PeriodEnd , Avg(tblQA_RollingMonths.AccuracyScore) AS AvgScore FROM tblQA_RollingMonths WHERE tblQA_RollingMonths.LoggedMonthYear In (SELECT TOP 3 TEMP.LoggedMonthYear FROM tblQA_RollingMonths as Temp WHERE TEMP.EmployeeID = tblQA_RollingMonths.EmployeeID and TEMP.Task = tblQA_RollingMonths.Task ORDER BY cdate(TEMP.LoggedMonthYear) DESC) GROUP BY tblQA_RollingMonths.Task , tblQA_RollingMonths.EmployeeID; John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County buzzmcduffie wrote: Dale, I know you are really getting frustrated with me but this help is invaluable to me! I must have put the cdate statement in the wrong place because it's still giving me an average of all dates - not just the last 3 months of data.. SELECT tblQA_RollingMonths.Task, tblQA_RollingMonths.EmployeeID, Count(tblQA_RollingMonths.LoggedMonthYear) AS NumberOfMonths, Min(tblQA_RollingMonths.LoggedMonthYear) AS PeriodBegin, Max(tblQA_RollingMonths.LoggedMonthYear) AS PeriodEnd, Avg(tblQA_RollingMonths.AccuracyScore) AS AvgScore FROM tblQA_RollingMonths WHERE ((([tblQA_RollingMonths].[LoggedMonthYear]) In (SELECT TOP 3 tblQA_RollingMonths.LoggedMonthYear FROM tblQA_RollingMonths as Temp WHERE Temp.EmployeeID = tblQA_RollingMonths.EmployeeID and Temp.Task = tblQA_RollingMonths.Task ORDER BY cdate(tblQA_RollingMonths.LoggedMonthYear) DESC))) GROUP BY tblQA_RollingMonths.Task, tblQA_RollingMonths.EmployeeID; can we spit it up so we first get the last 3 months of data for each and then query to get the average so I can see what months are produced for each employee? Thanks! |
Thread Tools | |
Display Modes | |
|
|