General discussion

Locked

Question on Join Query in Access 2003

By Hunterzh ·
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)

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by jleather In reply to Question on Join Query in ...

I think I have what you need. I was able to reproduce your problem given your SQL statement. Instead of messing with multiple joins in one fell swoop I split the process up into three queries. First I created two queries, one to give the total process workamount (called join1) and the second for outsource work amount (called join2). The SQL for these are (note you do not need to use SQL directly as I used the query builder for all this):

Join1:
SELECT [first table].[part number], Sum([second table].[process workamount]) AS [SumOfprocess workamount]
FROM [first table] INNER JOIN [second table] ON [first table].[part number] = [second table].[part number]
GROUP BY [first table].[part number];

Join2:
SELECT [first table].[part number], Sum([third table].[outsource work amount]) AS [SumOfoutsource work amount]
FROM [first table] INNER JOIN [third table] ON [first table].[part number]=[third table].[part number]
GROUP BY [first table].[part number];

Then I created the third join (called join1AND2) using the two above:
SELECT join1.[part number], join1.[SumOfprocess workamount], join2.[SumOfoutsource work amount]
FROM join1 INNER JOIN join2 ON join1.[part number] = join2.[part number];

Hope this helps . . .

John Leather

Collapse -

by Hunterzh In reply to Question on Join Query in ...

This question was closed by the author

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums