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];