Hi!
Can anyone tell me why the "Total Hours" field is not calculating correctly based on the below SQL?
The Query is summarizing the details by employee so that we can have the total hours worked or total Pieces completed for each job. For example, I have 1 employee that worked 47 hours in one job. They should earn $404.2 based on their pay rate for that job. Instead it is saying 1666 hours and $5,189.10 earned.
Here is the SQL:
Code:
SELECT tbl_WorkSummary.EmployeeName, tbl_WorkSummary.Company, tbl_WorkSummary.Hourly_Description, Sum(tbl_WorkSummary.[Hours]) AS [Total Hours], Sum(IIf([tbl_WorkSummary]![Hourly_Description]=[tbl_HourlyJobs_Rates]![Hourly_Description],[tbl_WorkSummary]![Hours]*[tbl_HourlyJobs_Rates]![Hourly_Wages])) AS [Hourly Earned], tbl_WorkSummary.PieceWork_Description, Sum(tbl_WorkSummary.Pieces) AS SumOfPieces, Sum([tbl_WorkSummary]![Pieces]*[tbl_PieceWork]![PW_Wages]) AS [Pieces Earned]
FROM (tbl_WorkSummary LEFT JOIN tbl_PieceWork ON tbl_WorkSummary.PieceWork_Description = tbl_PieceWork.PW_Description) LEFT JOIN tbl_HourlyJobs_Rates ON tbl_WorkSummary.Hourly_Description = tbl_HourlyJobs_Rates.Hourly_Description
GROUP BY tbl_WorkSummary.EmployeeName, tbl_WorkSummary.Company, tbl_WorkSummary.Hourly_Description, tbl_WorkSummary.PieceWork_Description, tbl_WorkSummary.JobType
ORDER BY tbl_WorkSummary.EmployeeName;
I tried removing all of the fields that perform other calculations to nail down where the issue is however that didn't change the issue. It's a simple SUM function in that field so I am not sure where else to look to figure it out.
Thank you so much for whatever help you can provide.