Need help with join types, getting duplicate results

[carlym]

Please be patient with me as I am a newbie. I have four separate queries, all exactly the same layout. When I join them I am getting a cartesian error which duplicates entries in my query results for the first of the 4 queries that are joined. (NO, my tables do not have duplicate entries in them, however they do all share the same vendor names which appear multiple times in some tables. ) I have each table joined by the "Vendor Name", which is a lookup field in each table. My table "Active Vendors" is the source from which the lookup field gets it's info. Because each individual table may have multiple entries for the same [Vendor Name], when I run my query, query results in columns for [1q13] are correct, but query results in columns for [4q12] are duplicating by the exact number of entries in [1q13] for that particular vendor. (See attached picture) I suspect this will also occur when I begin entering info in the tables for 2q13 and 3q13. I am certain that this is simply the result of an incorrect join, but I have no idea how to format the join so that duplicate entries do not appear. Any assistance is greatly appreciated. Here is my sql for the joined queries.

SELECT [Active Vendors].VendDBA, [4Q12 fiscal vendor analysis query].[SumOfQuarterly Volume], [4Q12 fiscal vendor analysis query].SumOfNetQtrlyRebate, [4Q12 fiscal vendor analysis query].[SumOfAnnual Rebate Amount], [1q13 fiscal vendor analysis query].[SumOfQuarterly Volume], [1q13 fiscal vendor analysis query].SumOfNetQtrlyRebate, [1q13 fiscal vendor analysis query].[SumOfAnnual Rebate Amount], [2q13 fiscal vendor analysis query].[SumOfQuarterly Volume], [2q13 fiscal vendor analysis query].SumOfNetQtrlyRebate, [2q13 fiscal vendor analysis query].[SumOfAnnual Rebate Amount], [3q13 fiscal vendor analysis query].[SumOfQuarterly Volume], [3q13 fiscal vendor analysis query].SumOfNetQtrlyRebate, [3q13 fiscal vendor analysis query].[SumOfAnnual Rebate Amount]


FROM ((([Active Vendors] LEFT JOIN [4Q12 fiscal vendor analysis query] ON [Active Vendors].VendDBA = [4Q12 fiscal vendor analysis query].VendDBA) LEFT JOIN [1q13 fiscal vendor analysis query] ON [Active Vendors].VendDBA = [1q13 fiscal vendor analysis query].VendDBA) LEFT JOIN [2q13 fiscal vendor analysis query] ON [Active Vendors].VendDBA = [2q13 fiscal vendor analysis query].VendDBA) LEFT JOIN [3q13 fiscal vendor analysis query] ON [Active Vendors].VendDBA = [3q13 fiscal vendor analysis query].VendDBA;

I am not an advanced user by any means so if you reply, please try and explain in detail. Thank you in advance!

Carlym

[June7]

'Duplicates' will exist if a table/query is joined to several tables/queries with which there is a 'many' relationship.

Some data output is often best done on a report/subreport arrangement.

[carlym]

'Duplicates' will exist if a table/query is joined to several tables/queries with which there is a 'many' relationship.

Some data output is often best done on a report/subreport arrangement.

Thank you for your reply, however, when I generate a report, the report simply duplicates the query and I still end up with the same duplicated records that appeared in the query.

[June7]

Don't use that same all-in-one query for the report. Build report/subreport(s). Main report bound to parent table and subreports hold related child table records.