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