SImple query my foot! Need help with potential relationship problem.

[Strangeway]

Ok, here's the deal. I'm hoping it is something easy to fix and that I'm just a boob.

I have three tables that are linked by a relationship.
First: A list of employees with a field indicating each's unique employee identifier (CLOCK_NUMBER.)

Second: A table tracking the number of units processed by each employee. There is a relationship linking the employee's clock number on this table to CLOCK_NUMBER on the employee list.

Three: A table tracking any errors that were made by an associate for a given day. This table is also linked to the CLOCK_NUMBER field on the employee list.

ISSUE: I'm building a query that spits out the number of errors made by month compared to the number of units processed. Sounds simple. I add the PICKED_UNITS table to the query, and everything works fine. As soon as I add the ERRORS_MADE table to the query, suddenly the picked units jump by 7 or 8 times (taking a picker's total from say 1170 units picked to over 8000 units picked.)

Does anyone know where I screwed up? I don't mind looking stupid, I just want to get it fixed!

 

[Strangeway]

Ok, this is weird. When I first add the ERRORS_MADE table to the query and switch to Datasheet view, all of the PICKED_UNITS totals are totally messed up. However, if I sort the query by any field (example, employee's last name) then after sorting all of the values are correct. Huh???

 

[Remou]

You will need nested queries, I think:

SELECT T.CLOCK_NUMBER, P.TotalPicks, E.TotalErrs 
FROM (employees T INNER JOIN 
    (SELECT CLOCK_NUMBER, Sum(PICKED_UNITS) As TotalPicks
     FROM PICKED_UNITS 
     GROUP BY CLOCK_NUMBER) P
ON T.CLOCK_NUMBER = P.CLOCK_NUMBER)
INNER JOIN 
    (SELECT CLOCK_NUMBER, Sum(ERRORS_MADE) As TotalErrs
     FROM ERRORS_MADE 
     GROUP BY CLOCK_NUMBER) E
ON T.CLOCK_NUMBER = E.CLOCK_NUMBER

 

[Strangeway]

Thank you.