Select Query has unexpected multiple records (MS Access)

Question

I may just be tired, but I can't seem to figure out what is happening. I have two queries. Query 1 has 10 fields. Query 2 has 5 fields. Fields 1 through 3 are the same in both queries. I am trying to write a select query and simple add field 5 from Query2 to Query1 so my result should be Query1 with an additional field (Query2.field5).

I join the first 3 fields from both queries and choose select all records in query1 and only those in query2 that match. I don't know sql, so I use query designer. I expect to get the same number of records that I have from query 1 but instead I have 3 times or more. Can someone tell me what I am doing wrong. I even tried reversing the joins but still seem to get the same extra records.

Thanks in advance.

Here is the query:

The fields that are in common are DivisionName, SupplierID = VendorID and CommodityName.

SELECT [Part Revenue Exposed Query P1].DivisionName, 
        [Part Revenue Exposed Query P1].CommodityName, 
        [Part Revenue Exposed Query P1].SupplierName, 
        [Part Revenue Exposed Query P1].PartNumber, 
        [Part Revenue Exposed Query P1].PartDescription, 
        [Part Revenue Exposed Query P1].BUCode, 
        [Part Revenue Exposed Query P1].ProductLine, 
        [Vendor Risk Score Query].VendorScore

FROM [Part Revenue Exposed Query P1] LEFT JOIN [Vendor Risk Score Query] 

ON ([Part Revenue Exposed Query P1].DivisionName = [Vendor Risk Score Query].DivisionName) 
    AND ([Part Revenue Exposed Query P1].SupplierID = [Vendor Risk Score Query].VendorID) 
    AND ([Part Revenue Exposed Query P1].CommodityName = [Vendor Risk Score Query].CommodityName);

show your table structure and indicate their cardinalities. Which are the primary keys of two tables. — Joe Taras
Hi, thanks for the response. Code was added. These aren't tables, but queries of queries. Only Supplier ID is a primary key in a source table... the other fields are not, but I could change it to use IDs instead of names. — user3203169
@User....try using INNER JOIN instead of LEFT JOIN. LEFT JOIN gives you records when the right side of the JOIN contains no match. answers.yahoo.com/question/index?qid=20070427082608AA7DwSh — MikeTWebb
@User...without knowing you data it's hard to tell. But, the most likely reason for the duplicates is that your join returns more than 1 row from Q2 for Q1 — MikeTWebb

CRAFTY DBA CRAFTY DBA · Accepted Answer · 2014-01-31T16:00:58

Check you join condition.

   AND ([Part Revenue Exposed Query P1].SupplierID = [Vendor Risk Score Query].VendorID)

Is supplier to vendor correct?

You said the first three fields are the same. But it only shows two the same.

Select Query has unexpected multiple records (MS Access)

2 Answers