Aggregate Query sum field not calculating correctly

[jbeets]

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:

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.

[ranman256]

careful of your sums. sometimes you cant always sum 2 things in the same query. it can skew results.
but some things can.

try Q1 to sum hrs,
and Q2 to sum earned.
this ensures the criteria is correct.
you can always make Q3 to join Q1 & 2.

[RayMilhon]

Take out the group by's and look for duplicate rows. The query will group those but the sum adds them all up. What I do is use a group by query for all columns and then a second query to sum those

[CJ_London]

1666 divided by 47 is not a whole number so suspect something other than multiple records could also be an issue

[jbeets]

thank you all for your help! I added another join between the two tables and that removed the duplicates that were apprently causing the issue. I really appreciate the help and your thoughts on the problem!