I have a table/form with fields "Hours", "Employee", "PartCode", "OperationCode", and "Quantity". For each entry, we enter the number of hours a particular employee spends on a certain operation associated with a particular component, and the number of parts he/she produced in that time frame. There will be thousands of these entries in time.
Basically what I want is for my query to tally total Hours and total Quantity based on PartCode and OperationCode (in the future I may also break it down by Employee, but not today). Here's my query:
SELECT [PartCodeList].PartCode AS PartCode, [OperationCodeList].OperationCode AS OperationCode, SUM([WorkLogHoursSub].Hours) AS Hours, Sum([WorkLogHoursSub].Quantity) AS Quantity
FROM PartCodeList, OperationCodeList, WorkLogHoursSub
WHERE (([WorkLogHoursSub].PartCode)=PartCodeList.PartCode) And (([WorkLogHoursSub].OperationCode)=OperationCodeList.OperationCode)
GROUP BY [PartCodeList].PartCode, [OperationCodeList].OperationCode;
I've entered the following information in the corresponding fields in the form as a test (PartCode / OperationCode / Hours / Quantity):
• H1 / B / 2 / 2
• H1 / C / 3 / 4
• H1 / D / 20 / 40
• H2 / B / 8 / 12
• H1 / C / 6 / 9
• H3 / D / 8 / 40
However, that query returns strange results for total hours and total quantity in the query. Results are below (Code[combined] / CorrectHours / ActualHours / CorrectQty / ActualQty):
• H1B / 2 / 64 / 2 / 64
• H1C / 9 / 243 / 13 / 351
• H1D / 20 / 480 / 40 / 960
• H2B / 8 / 256 / 12 / 384
• H3D / 8 / 192 / 40 / 960
Strangely enough, although the correct total hours for H2B and H3D are identical, the query returns differing results. On the flip side, the correct total quantity for H1D and H3D is 40, and the query returned identical 960s for both of them....
Whoa, just figured something out. When the OperationCode is "B", it's multiplying the correct total by 32. When the code is "C", it's multiplying by 27. And when the code is "D", it's multiplying by 24. Any idea why that's happening?
EDIT: "I" is multiplying by 1 and so getting the correct results.... lol
EDIT2: Only one PartCode is associated with the "I" OperationCode, whereas 32 PartCodes are associated with the "B" OperationCode, 27 with "C", and 24 with "D". So it appears the query is multiplying by the number of times the OperationCode appears in the OperationCodeList table, regardless of whether or not it's associated with the correct PartCode.
EDIT3: Any idea how to fix that?
SOLUTION: I changed the query so it also pulls the PartCode from the OperationCodeList, rather than a separate table. This has solved the problem. Fixed query:
SELECT [OperationCodeList].PartCode AS PartCode, [OperationCodeList].OperationCode AS OperationCode, SUM([WorkLogHoursSub].Hours) AS Hours, Sum([WorkLogHoursSub].Quantity) AS Quantity
FROM OperationCodeList, WorkLogHoursSub
WHERE (([WorkLogHoursSub].PartCode)=OperationCodeList.PartCode) And (([WorkLogHoursSub].OperationCode)=OperationCodeList.OperationCode)
GROUP BY [OperationCodeList].PartCode, [OperationCodeList].OperationCode;