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

    combine 2 queries to summarize the results

    Hello,

    I have two queries that I need to combine into one aggregate query. Individually the queries give me the exact data that I need but when I attempt to create a third query that combines the data the resutls get funky.

    The jobs are split into two lists. One is for the hourly jobs and the other is for the jobs that are calculated by the pieces completed. There are 48 different jobs that are calculated by the pieces completed.

    When I run the two queries together the results are not showing up properly. Each employee is showing results for each job even if they didn't work them. Also the data is duplicated. I am guessing I am just going about this all wrong but I really don't know what to do.

    Below is the SQL for the 3 queries.

    Code:
    SELECT tbl_EmployeeTimeTracking.EmployeeName, tbl_EmployeeTimeTracking.JobType, tbl_EmployeeTimeTracking.JobDescription, Sum(tbl_EmployeeTimeTracking.Pieces) AS SumOfPieces, Sum([tbl_EmployeeTimeTracking]![Pieces]*[tbl_JobDescriptions]![Wages]) AS [PieceWork Earned]
    FROM tbl_EmployeeTimeTracking INNER JOIN tbl_JobDescriptions ON tbl_EmployeeTimeTracking.JobDescription = tbl_JobDescriptions.Description
    WHERE (((tbl_EmployeeTimeTracking.DateWorked)>=[Start Date] And (tbl_EmployeeTimeTracking.DateWorked)<=[End Date]))
    GROUP BY tbl_EmployeeTimeTracking.EmployeeName, tbl_EmployeeTimeTracking.JobType, tbl_EmployeeTimeTracking.JobDescription
    ORDER BY tbl_EmployeeTimeTracking.EmployeeName;
    
    Code:
    SELECT tbl_EmployeeTimeTracking.EmployeeName, tbl_EmployeeTimeTracking.WorkType, Sum(tbl_EmployeeTimeTracking.WorkTypeHours) AS SumOfWorkTypeHours, Sum(IIf([tbl_EmployeeTimeTracking]![WorkType]=[tbl_EmployeeWages2]![WorkType],[WorkTypeHours]*[Wage],Null)) AS [Hourly Earned]
    FROM tbl_EmployeeTimeTracking INNER JOIN tbl_EmployeeWages2 ON (tbl_EmployeeTimeTracking.EmployeeName = tbl_EmployeeWages2.[Employee Name]) AND (tbl_EmployeeTimeTracking.WorkType = tbl_EmployeeWages2.WorkType)
    WHERE (((tbl_EmployeeTimeTracking.DateWorked)>=[Start Date] And (tbl_EmployeeTimeTracking.DateWorked)<=[End Date]))
    GROUP BY tbl_EmployeeTimeTracking.EmployeeName, tbl_EmployeeTimeTracking.WorkType
    ORDER BY tbl_EmployeeTimeTracking.EmployeeName;
    



    This is the query that combines the two results:

    Code:
    SELECT qry_WorkType_Summary.EmployeeName, qry_WorkType_Summary.WorkType, qry_WorkType_Summary.[Hourly Earned], qry_JobType_Summary.JobType, qry_JobType_Summary.JobDescription, qry_JobType_Summary.[PieceWork Earned]
    FROM qry_WorkType_Summary, qry_JobType_Summary
    GROUP BY qry_WorkType_Summary.EmployeeName, qry_WorkType_Summary.WorkType, qry_WorkType_Summary.[Hourly Earned], qry_JobType_Summary.JobType, qry_JobType_Summary.JobDescription, qry_JobType_Summary.[PieceWork Earned];
    

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Without a join between the queries you'll get a Cartesian product. You need to join on the appropriate field(s).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Thank you pbaldy; you are always so kind to help me!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    Location
    USA
    Posts
    64
    And here is something related that you might find helpful...

    Union
    http://www.w3schools.com/sql/sql_union.asp

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

Similar Threads

  1. Replies: 2
    Last Post: 01-29-2019, 12:05 AM
  2. Replies: 1
    Last Post: 05-06-2013, 03:32 PM
  3. Combine Results of Two Queries
    By Ganymede in forum Queries
    Replies: 3
    Last Post: 12-28-2012, 02:00 PM
  4. Replies: 1
    Last Post: 10-23-2012, 02:04 PM
  5. Combine queries results in forms
    By frasilvio in forum Queries
    Replies: 12
    Last Post: 01-10-2008, 01:34 AM

Tags for this Thread

Posting Permissions

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