473,224 Members | 1,476 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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

4 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;
Jan 31 '21 #1
4 2958
CougarX6
4 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);
Feb 3 '21 #2
CougarX6
4 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...
Feb 3 '21 #3
CougarX6
4 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!
Feb 6 '21 #4
isladogs
451 Expert Mod 256MB
Sorry that nobody answered your conversation with yourself.
Glad to see you worked out the solution.
Feb 8 '21 #5

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

Similar topics

3
by: Greg | last post by:
I am trying to figure out how to create a report that will return orders current, over 30, over 60, over 90, and over 120. In one table I have Cust_Code, INV_Date, INV_Due_Date, INV_Bal and in...
2
by: Andrea | last post by:
I'm having some difficulty creating a report in Access and I need some suggestions. My company issues "Return Authorizations" when customers need to return products. A customer calls in and we...
1
by: longtim | last post by:
I have been having endless difficulty creating reports/queries that set any relevent parameters from controls in forms. I am creating an application under access 2003 but will target access...
8
by: Brent White | last post by:
If you need more information, I'd be glad to give it, but I am developing an ASPX page so that the user can select multiple values for a specific field, then pass them on (using Crystal Reports 10...
3
by: erick-flores | last post by:
Hello all I am creating an Expense Report. There are to types of expenses: Company expenses and Reimb Expeneses. The company expenses is under "Expense Details Co" table and Reimb Expenses is...
4
by: Bucco | last post by:
I installed python 2.5b3 on my windows XP sp2 box without any issues. I can double click the python program, and idle comes up in the command line window. However when I run python from the...
3
by: cassey14 | last post by:
Hi I hope someone help me on this.. I need to attach any files in my form like word,excel etc and save it on database..and also I need to open it at the same time..please help me... and also im...
6
by: Pep | last post by:
Firstly, I'm not sure if this is the right group for this query, so please forgive me if I am wrong. My problem is that most users I distribute my software to cannot install it on their systems...
0
by: rmurgia | last post by:
I am trying to create an Access report programmatically. So far I have been able to accomplish everything except the following: 1) I cannot determine the code to create a Page Footer Set...
0
by: locatelli | last post by:
having issues with BizTalk to be able to see newly created DB2 stored procs and DB2 Alias. We have Alias and Stored procs created a few years ago that BizTalk can see.
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.