Results 1 to 5 of 5
  1. #1
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    40

    Aggregate Query sum field not calculating correctly

    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.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,997
    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.

  3. #3
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,034
    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

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,995
    1666 divided by 47 is not a whole number so suspect something other than multiple records could also be an issue

  5. #5
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    40
    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!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 01-20-2018, 03:40 PM
  2. Replies: 1
    Last Post: 10-17-2016, 07:57 AM
  3. Replies: 4
    Last Post: 03-08-2016, 03:22 PM
  4. Replies: 3
    Last Post: 12-28-2013, 09:13 AM
  5. Replies: 23
    Last Post: 06-30-2010, 02:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums