In Access 2003, I?ve designed a database, let?s say, with three tables: one is basic info, with part number, description, and so on; second is process work, recording process work for items in first table, with ID, part number, amount, and so on (first table has a one-to-many relation with second table); third table is similar as second table, for outsource work, recording outsource work for items in first table, with ID, part number, outsource amount, and so on (first table also has a one-to many relation with third table).
Now I want to design a Query with part number, description, sum of process work, sum of outsource work. But it always has wrong result. My Query is like:
Select [first table].[part number], [first table].[description], Sum([second table].[process work amount]), Sum([third table].[outsource work amount])
From ([first table] left join [second table] on [first table].[part number]= [second table].[part number]) left join [third table] on [first table].[pat number]= [third table].[pat number]
Group by [first table].[part number], [first table].[description]
The result is: if I only select Sum from second table, the result is correct; or only select Sum from third table, the result is also correct. But when I select Sum from the two tables, the Sum result of third table is wrong. For example, if we have 4 process works for part number 1, and 1 outsource work for part number 1. The Sum result for part number 1 will be 4 process works and 4 outsource works (duplicate)