Aggregate Query Showing dulplicate records

[jbeets]

Hello,

I know I am missing something silly but I'm struggling to identify what it is.



I have a basic query with 2 tables. Table 1 is tbl_EmployeeTimeTracking and includes the following fields : DateWorked, EmployeeName, Worktype, Worktypehours, JobType, JobDescription, JobTypeHours, Pieces, EmployeeID.

Table 2 is tbl_EmployeeWages and includes the following fields: EmployeeNumber, Employee, WorkType, Description, Wage


I am attempting an aggregate query to summarize the work by Employee, WorkType, Hours worked by each work type and the total earned by worktype. The fields in my query are as follows:

EmployeeName, WorkType, HoursWorked (this field is summed), Total Earned (HoursWorked * [tbl_EmployeeWages]![Wages]

The tables have a relationship using EmployeeID

When I run the query like this I get the employee duplicated - the hours are broken in half between two of the same Worktypes and a diffierent salary is applied to each entry.
For Example the results would look like this:
Jane Doe, Thrift (this is the only work type entered for this employee), 48, 0

Jane Doe, Thrift (this is the only work type entered for this employee), 48, 51.60

The results should read:
Jane Doe, Thrift, 96, 825.60

For Worktype, each employee has the possibility of 2 worktypes, so on the tbl_EmployeeWages employee 1 would have worktype Thift and worktype Laundry each with their own associated wage.

I am sorry this post is so long but i hope i explained everything clear enough for someone to help me understand what I need to fix and thank you in advance!

[pbaldy]

Might help to see the SQL of the query. I'd guess there are multiple records in the second table for that employee, and you didn't join on the work type field.

[jbeets]

See... I told you it was something silly. I didnt do the join correctly....Thank you!

[jbeets]

Might help to see the SQL of the query. I'd guess there are multiple records in the second table for that employee, and you didn't join on the work type field.

This is the SQL

SELECT tbl_EmployeeTimeTracking.EmployeeName, tbl_EmployeeTimeTracking.WorkType, Sum(tbl_EmployeeTimeTracking.WorkTypeHours) AS SumOfWorkTypeHours, IIf([tbl_EmployeeTimeTracking]![WorkType]=[tbl_EmployeeWages]![WorkType],[WorkTypeHours]*[Wage],Null) AS Earned
FROM tbl_EmployeeTimeTracking INNER JOIN tbl_EmployeeWages ON (tbl_EmployeeTimeTracking.EmployeeName = tbl_EmployeeWages.Employee) AND (tbl_EmployeeTimeTracking.WorkType = tbl_EmployeeWages.WorkType)
GROUP BY tbl_EmployeeTimeTracking.DateWorked, tbl_EmployeeTimeTracking.EmployeeName, tbl_EmployeeTimeTracking.WorkType, IIf([tbl_EmployeeTimeTracking]![WorkType]=[tbl_EmployeeWages]![WorkType],[WorkTypeHours]*[Wage],Null)
HAVING (((tbl_EmployeeTimeTracking.DateWorked)>=[Start Date] And (tbl_EmployeeTimeTracking.DateWorked)<=[End Date]))
ORDER BY tbl_EmployeeTimeTracking.EmployeeName;

It was working after fixing the join however I added in a field [Dateworked] with a parameter for the user to select the date range. Now I am showing a record for every entry within that date range. Any suggestion on how to simply show the summarized results within the date range?

[pbaldy]

In design view change Group By to Where on that field. In SQL view, that will drop it from the SELECT and GROUP BY clauses, leaving it in the HAVING clause (which should really be a WHERE clause for performance, but probably won't be noticeable).

[jbeets]

You are amazing! Thank you so much for helping me!

[pbaldy]

Happy to help! If "amazing" is defined as "made the same mistake 800 times and finally learned from it", then yes, I'm amazing.

[ssanfu]

.... If "amazing" is defined as "made the same mistake 800 times and finally learned from it", then yes, I'm amazing.

Now THAT is funny!!!