I don't see anything syntactically incorrect about it. One of the things I'd encourage you to do, when trying to figure out what is wrong with SQL, is to space it out, indent it, and give each field a line of its own.
Copy it to the clipboard and do this in a blank notepad, since Access will "helpfully" squash it all back together into unreadable form every time you switch views.
You can even give each parenthesis that is not part of an expression a line and indentation of its own, if it helps you know which one goes with which one.
This can often help you catch goofs that are obvious to a compiler, but not to a human. No apparent goofs here, though.
Code:
SELECT
Employees.Supervisor,
[Work Hours].[Full Name],
[Work Hours].[Date Worked],
[Work Codes].[Productivity Type],
[Work Codes].WorkCode,
Sum([Work Hours].Hours) AS Hours,
[Work Hours].Description,
[Work Hours].ManualCount,
[Employees-Active].WindowsID AS SupWinID
FROM
(Employees
INNER JOIN
([Work Hours]
LEFT JOIN
[Work Codes]
ON [Work Hours].[Work Code] = [Work Codes].ID
)
ON Employees.[Full Name] = [Work Hours].[Full Name]
)
INNER JOIN
[Employees-Active]
ON Employees.Supervisor = [Employees-Active].[Employee Name]
WHERE (((Employees.EmpType)="RS"))
GROUP BY
Employees.Supervisor,
[Work Hours].[Full Name],
[Work Hours].[Date Worked],
[Work Codes].[Productivity Type],
[Work Codes].WorkCode,
[Work Hours].Description,
[Work Hours].ManualCount,
[Employees-Active].WindowsID
HAVING
( ( (
[Work Hours].[Date Worked]
)
BETWEEN
(
Date()-Weekday(Date())
)-6
AND
Date()-Weekday(Date())
) )
ORDER BY
Employees.Supervisor,
[Work Hours].[Full Name],
[Work Hours].[Date Worked];
I don't like the look of that -6 sitting outside the parens, so I'd either move it inside the parens for clarity, or I'd more likely use the DateAdd function for even more clarity and certainty.
Code:
HAVING
( [Work Hours].[Date Worked]
BETWEEN
DateAdd ("d", -6 - Weekday(Date()), Date())
AND
DateAdd ("d", - Weekday(Date()), Date())
)
Of course, it would also make sense to move all the HAVING clause up to the WHERE clause, since it's not testing against GROUP/aggregate data anyway.
Code:
WHERE
( ( (Employees.EmpType)="RS" )
AND ( [Work Hours].[Date Worked]
BETWEEN
DateAdd("d", - 6 - Weekday(Date()), Date())
AND
DateAdd("d", - Weekday(Date()), Date())
)
)