Author |
Topic |
sandeebee123
Starting Member
2 Posts |
Posted - 2013-08-05 : 11:34:02
|
I am trying to run an Access query that someone completed in SQL. Below is the query: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 SupWinIDFROM (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].WindowsIDHAVING ((([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 am getting the following error:Compile error. in query expression '((([Work.Hours].[Date Worked]) Between (date()-Weekday(Date()))-6 And Date ()-Weekday(Date()))'. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-05 : 11:53:19
|
Dup of this? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=187340 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-05 : 12:08:21
|
[code]SELECT e.Supervisor, wh.[Full Name], wh.[Date Worked], wc.[Productivity Type], wc.WorkCode, SUM(wh.[Hours]) AS [Hours], wh.[Description], wh.ManualCount, ea.WindowsID AS SupWinIDFROM dbo.Employees AS eINNER JOIN dbo.[Work Hours] AS wh ON wh.[Full Name] = w.[Full Name]LEFT JOIN dbo.[Work Codes] AS wc ON wc.ID = wh.[Work Code]INNER JOIN dbo.[Employees-Active] AS ea ON ea.[Employee Name] = e.SupervisorWHERE e.EmpType = 'RS' AND wh.[Date Worked] >= DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()) / 7 * 7, -8) AND wh.[Date Worked] < DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()) / 7 * 7, -1)GROUP BY e.Supervisor, wh.[Full Name], wh.[Date Worked], wc.[Productivity Type], wc.WorkCode, wh.[Description], wh.ManualCount, ea.WindowsIDORDER BY e.Supervisor, wh.[Full Name], wh.[Date Worked];[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|