Share via

New to SQL in Microsoft Access and having major issues

Mark Davis 1 Reputation point
2021-01-31T20:49:37.337+00:00

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;

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
830 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mark Davis 1 Reputation point
    2021-01-31T22:09:06.283+00:00

    Access wasn't an option when it asked for Tags. The errors were errors in Syntax, in the Join, in Select etc, depending on where I made edits. I've also removed the document. I'll probably just remove the entire post and try to find the correct forum.

    Thanks for the response!

    0 comments
  2. C Data 6 Reputation points
    2021-02-02T23:13:20.447+00:00

    think in terms of datasets - which in this case are queries

    Qry1 : date ranged so you have all the correct records - and only those records

    Qry2 : use Qry1 as the data set: make it an Aggregate type grouping by Employee and Sum the hours...2 fields.

    Qry3: use Qry2 as the data set with Employee table to join for full name, possibly other core employee info... and here add your calculated fields to calc the over time, etc.

    in this design when you run Qry3 - the Qry1 and Qry2 run as well...but in this design you can look more easily at each step so to make it easier to debug and refine. Use the query Design View feature.

    0 comments