Ambiguous outer join in MS Access

Question

Trying to create an outer join on two other joined tables when recieving this error - I just dont see how to create two separate queries to make it work. Subqueries don't seem to work either, any help appreciated. I get errors for the below query, thanks.

SELECT 
    CardHeader.CardID, CardHeader.CardDescription, CardHeader.GloveSize,     
    CardHeader.GloveDescription, CardDetail.Bin, CardDetail.ItemID, Items.ItemDescription, 
    Items.VCatalogID, CardDetail.ChargeCode, CardDetail.Quantity, Items.Cost, CardColors.ColorID
FROM
    ((Items 
INNER JOIN 
    (CardHeader INNER JOIN CardDetail ON CardHeader.CardID = CardDetail.CardID) ON Items.ItemID = CardDetail.ItemID) 
LEFT JOIN 
    CardColors ON CardDetail.ItemID = CardColors.ItemID) 
INNER JOIN 
    Colors ON CardColors.ColorID = Colors.ID
ORDER BY 
    CardHeader.CardID;

I tried the following which runs but asks for the following parameters (which it shouldnt)

CardHeader.ID, MainQry.CardID

SELECT 
    MainQry.ID, MainQry.CardDescription, MainQry.GloveSize, 
    MainQry.GloveDescription, MainQry.Bin, MainQry.ItemID,
    MainQry.ItemDescription, MainQry.VCatalogID, MainQry.ChargeCode, 
    MainQry.Quantity, MainQry.Cost, SubQry.ColorID
FROM
    (SELECT 
         CardHeader.ID, CardHeader.CardDescription, CardHeader.GloveSize, 
         CardHeader.GloveDescription, CardDetail.Bin,
         CardDetail.ItemID, Items.ItemDescription, Items.VCatalogID, 
         CardDetail.ChargeCode, CardDetail.Quantity, Items.Cost 
     FROM 
         Items 
     INNER JOIN 
         (CardHeader 
     INNER JOIN 
         CardDetail ON CardHeader.CardID = CardDetail.CardID) ON Items.ItemID = CardDetail.ItemID
    )  AS MainQry 
LEFT JOIN 
    (SELECT 
         CardColors.ItemID, CardColors.ColorID 
     FROM 
         CardColors 
     INNER JOIN 
         Colors ON CardColors.ColorID = Colors.ID) AS SubQry ON MainQry.ItemID = SubQry.ItemID
ORDER BY 
    MainQry.CardID;

See http://stackoverflow.com/questions/11734577/ambiguous-left-joins-in-ms-access/11735741#11735741 — Fionnuala, Aug 02 '12 at 18:56
Why have you got this line `INNER JOIN Colors ON CardColors.ColorID = Colors.ID`? All you are selecting is `CardColors.ColorID`, so the Color table is not used. — Fionnuala, Aug 02 '12 at 19:05
Is this CardHeader.ID supposed to be CardHeader.CardID ? I think it is. — Fionnuala, Aug 02 '12 at 19:56

score 1 · Accepted Answer · answered Aug 03 '12 at 13:46

The second SQL statement can be corrected by reference to the first statement and the error. The error is that both CardHeader.ID and MainQry.CardID are prompting for a parameter, which indicates that the inner statement should include CardHeader.CardID, rather than CardHeader.ID

Ambiguous outer join in MS Access

1 Answers1