467,913 Members | 1,796 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,913 developers. It's quick & easy.

New to SQL and having issues creating a report to calc OT hours and dollars

Nibble
I am tasked with tracking Overtime hours worked by Department/Employee. Our work week is 35 hours, first 5 additional is Straight Time, any additional is OT (at time and a half). Tables collect Week of Date, Employee, # of OT Hours and Department. I initially attempted to use a running sum in the report and can't get it to work correctly. Now I've gone to SQL and trying to Total the # of OT Hours by Dept/Employee/Week in the Query, then do the calc for ST and then OT. I can't get the SUM of OTHours to add up the total # of hours by Dept/Employee/Week, it simply repeated the # of OT hours for each record. Here is what I have for SQL, partially based on information I found dating back to 2010 (which is giving me errors now). Not sure how to make this work and would appreciate any direction. I also attached the report I had from using Running Sums, just couldn't get the Sums to work if there was more than ONE employee in a Dept.

SELECT EmployeeData.EmployeeName, EmployeeData.[Pay Rate], EmployeeData.OT, EmployeeData.[Salary Type], EmployeeDataOT.OTProgramArea, EmployeeDataOT.WeekOfDate, EmployeeDataOT.DateEntry, EmployeeDataOT.OTHours, [SubMH].[EmployeeTotalOT], IIf(([SubMH].[EmployeeTotalOT]>=5),(5*[Pay Rate]),[SubMH].[EmployeeTotalOT][Pay Rate]) AS STDollars, IIf(([Salary Type]="Hourly") And ([SubMH].[EmployeeTotalOT]>=5),(([SubMH].[EmployeeTotalOT]-5)[OT]),Null) AS OTDollars
FROM EmployeeData INNER JOIN
(
SELECT [OTProgramArea]
,[EmployeeName]
,[WeekOfDate]
,Sum(EmployeeDataOT.OTHours) AS EmployeeTotalOT
FROM [EmployeeDataOT]
GROUP BY EmployeeDataOT.OTProgramArea, EmployeeData.EmployeeName, EmployeeDataOT.WeekOfDate
) AS [subMH]
ON EmployeeDataOT (EmployeeData.EmployeeName = EmployeeDataOT.EmployeeNameOT) AND (EmployeeData.DepartmentUnit = EmployeeDataOT.DepartmentLookup)
HAVING (((EmployeeDataOT.OTHours)>0) AND ((EmployeeDataOT.DateEntry) Between [Week of Start Date] And [Week of End Date]))
ORDER BY EmployeeDataOT.OTProgramArea, EmployeeDataOT.OTProgramArea, EmployeeData.EmployeeName, EmployeeDataOT.WeekOfDate, EmployeeDataOT.DateEntry DESC;
4 Weeks Ago #1
  • viewed: 2429
Share:
4 Replies
Nibble
I think I cleaned up some of what I had cobbled together from that post I found in 2010. Still can't get the query to run. Initially I got: 'You have written a subquery that can return more than one field without using the 'EXISTS' reserved word in the main query's 'FROM' clause. Revise the 'SELECT' statement of the subquery to request only one field' So I moved the other 3 fields I'm grouping by in the subquery to the main query, leaving only the SUM field. Now I get the prompts to run the query, but get no results and a pop up that 'Only one record can be returned for this query'. Still not sure what I'm doing wrong... although I think it may be something in the two FROM clauses I'm using. My revised SQL is below... I would appreciate any feedback.

SELECT EmployeeDataOT.OTProgramArea, EmployeeData.EmployeeName, EmployeeDataOT.WeekOfDate, EmployeeDataOT.DateEntry, [Week Of Start Date] AS Expr1, [Week of End Date] AS Expr2, EmployeeData.[Pay Rate], EmployeeData.[Salary Type], EmployeeData.OT, EmployeeDataOT.OTHours, [EDOT].[EmployeeTotalOT], IIf(([EDOT].[EmployeeTotalOT]>=5),(5*[Pay Rate]),[EDOT].[EmployeeTotalOT]*[Pay Rate]) AS STDollars, IIf(([Salary Type]="Hourly") And ([EDOT].[EmployeeTotalOT]>=5),(([EDOT].[EmployeeTotalOT]-5)*[OT]),Null) AS OTDollars
FROM EmployeeData INNER JOIN EmployeeDataOT ON (EmployeeData.DepartmentUnit = EmployeeDataOT.DepartmentLookup) AND (EmployeeData.EmployeeName = EmployeeDataOT.EmployeeNameOT)
WHERE (((EmployeeDataOT.DateEntry) Between [Week Of Start Date] And [Week of End Date])) AND OTHours>0
ORDER BY EmployeeDataOT.DateEntry

(SELECT Sum([OTHours]) AS EmployeeTotalOT
FROM EmployeeData AS ED INNER JOIN [EmployeeDataOT] AS EDOT ON ED.EmployeeName = EDOT.EmployeeNameOT AND ED.DepartmentUnit = EDOT.DepartmentLookup
GROUP BY EDOT.OTProgramArea, ED.EmployeeName, EDOT.WeekOfDate);
4 Weeks Ago #2
Nibble
What I have is based off this post:
https://bytes.com/topic/access/answe...overtime-hours

If the subquery can only return ONE record, not sure how the report in the other post works. It seems like I'm trying to generate the same type of report.

Still need some help...
4 Weeks Ago #3
Nibble
Solved it by not including the date entry and othours as fields in the report, I can do without the individual detail in this report, just really need the totals by week, employee, program and grand total. I was able to do the SUM of othours in the query, as well as the calcs for ST$ and OT$, all without delving into the sql or using any subquery.

Thanks for all the help!
3 Weeks Ago #4
isladogs
Expert 128KB
Sorry that nobody answered your conversation with yourself.
Glad to see you worked out the solution.
3 Weeks Ago #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Greg | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.