YTD subquery for Not working and causing me grief
Help!
I wrote a query which is supposed to add MONTH and YTD hours BY DEPT BY PERIOD BY YEAR
The data pulls from the main table "Tbl_Hours_Actual" and then I'm trying to write a subquery off that table, but the YTD totals are goofy. If I remove "DeptNo" then YTD totals are fine. But I need it by Department.
Am I asking for too much in one query?
Help!
I wrote a query which is supposed to add MONTH and YTD hours BY DEPT BY PERIOD BY YEAR
The data pulls from the main table "Tbl_Hours_Actual" and then I'm trying to write a subquery off that table, but the YTD totals are goofy. If I remove "DeptNo" then YTD totals are fine. But I need it by Department.
Am I asking for too much in one query?
Code:
SELECT Year([Tbl_Hours_Actual].[RepDate]) AS TheYear, Month([Tbl_Hours_Actual].[RepDate]) AS TheMonth, Tbl_Hours_Actual.DeptNo, Sum(Tbl_Hours_Actual.Hours) AS MonthHours,
(SELECT Sum(A.Hours) AS YTD FROM Tbl_Hours_Actual AS A WHERE A.RepDate >= DateSerial(Year(Tbl_Hours_Actual.RepDate),1,1) AND A.RepDate < DateSerial(Year(Tbl_Hours_Actual.RepDate), Month(Tbl_Hours_Actual.RepDate)+1,1)) AS YTDHours, Tbl_Std_Hours.Hours, [MonthHours]/[Tbl_Std_Hours]![Hours] AS FTE
FROM Tbl_Hours_Actual INNER JOIN Tbl_Std_Hours ON Tbl_Hours_Actual.CalPeriod = Tbl_Std_Hours.CalPeriod
GROUP BY Year([Tbl_Hours_Actual].[RepDate]), Month([Tbl_Hours_Actual].[RepDate]), Tbl_Hours_Actual.DeptNo, Tbl_Std_Hours.Hours;
Attachments
Last edited: