Full Join on MS Access

Question

so I'm trying to do a full join on MS Access 2003 but just found out it did not support it. So I tried taking my two select statements and then joining one using LEFT join and making a UNION with the same statement but with a RIGHT join. Access gave me an error saying that there is something wrong with the JOIN command. Heres some sql...

SELECT tbl_Vendors.VendorName, tbl_Inventory.ItemNum, 
tbl_Inventory.Color,  tbl_Inventory.InInventory, 
tbl_Inventory.OutInventory, 
(tbl_Inventory.Stocks +   tbl_Inventory.InInventory - 
tbl_Inventory.OutInventory) AS Balance,  
tbl_Inventory.Weight, tbl_Inventory.CF,   
(tbl_Inventory.Weight *Balance) AS TotalWeight, 
(tbl_Inventory.CF * Balance) AS TotalCF, 
tbl_Inventory.NoteOrder, tbl_ItemHistory.orderDate, 
tbl_ItemHistory.POHistory, tbl_ItemHistory.InorOut, 
tbl_ItemHistory.Unit
FROM (tbl_Vendors INNER JOIN tbl_Inventory 
    ON tbl_Vendors.vid = tbl_Inventory.VendorID)
LEFT JOIN tbl_ItemHistory 
ON tbl_Inventory.ItemNum = tbl_ItemHistory.ItemNum
ORDER BY tbl_Inventory.ItemNum, tbl_ItemHistory.orderDate 

sorry if this is not in code format, access sql i guess is just normal text. this one is with only the left join. if you have any ideas, please say so. Thanks!

edit: 2 step joins,

SELECT tbl_Vendors.VendorName, tbl_Inventory.ItemNum, tbl_Inventory.Color, 
tbl_Inventory.InInventory, tbl_Inventory.OutInventory, 
(tbl_Inventory.Stocks+tbl_Inventory.InInventory-tbl_Inventory.OutInventory) AS Balance, 
tbl_Inventory.Weight, tbl_Inventory.CF, (tbl_Inventory.Weight*Balance) AS TotalWeight,    
(tbl_Inventory.CF*Balance) AS TotalCF, tbl_Inventory.NoteOrder, tbl_ItemHistory.orderDate, 
tbl_ItemHistory.POHistory, tbl_ItemHistory.InorOut, tbl_ItemHistory.Unit
FROM (tbl_Vendors INNER JOIN tbl_Inventory ON tbl_Vendors.vid = tbl_Inventory.VendorID) LEFT JOIN
tbl_ItemHistory ON tbl_Inventory.ItemNum = tbl_ItemHistory.ItemNum;
UNION ALL
SELECT tbl_Vendors.VendorName, tbl_Inventory.ItemNum, tbl_Inventory.Color, 
tbl_Inventory.InInventory, tbl_Inventory.OutInventory, 
(tbl_Inventory.Stocks+tbl_Inventory.InInventory-tbl_Inventory.OutInventory) AS Balance, 
tbl_Inventory.Weight, tbl_Inventory.CF, (tbl_Inventory.Weight*Balance) AS TotalWeight,    
(tbl_Inventory.CF*Balance) AS TotalCF, tbl_Inventory.NoteOrder, tbl_ItemHistory.orderDate, 
tbl_ItemHistory.POHistory, tbl_ItemHistory.InorOut, tbl_ItemHistory.Unit
FROM (tbl_Vendors INNER JOIN tbl_Inventory ON tbl_Vendors.vid = tbl_Inventory.VendorID) RIGHT  
JOIN tbl_ItemHistory ON tbl_Inventory.ItemNum = tbl_ItemHistory.ItemNum;

error: join expression not supported. The first piece of code was good for left outer join. i tried two left joins and that worked. its just not taking my right join...

Solution

Your ultimate goal is to emulate a FULL OUTER JOIN, but your first hurdle is that Access' database engine complains about your LEFT JOIN attempt. You need to first create a workable JOIN, and I can't spot what's wrong with the sample you provided.

Does Access accept this simplified version?

SELECT *
FROM
    (tbl_Vendors AS vend
    INNER JOIN tbl_Inventory AS inv
    ON vend.vid = inv.VendorID)
    LEFT JOIN tbl_ItemHistory AS hist
    ON inv.ItemNum = hist.ItemNum;

For the moment, we're not concerned about the field list or ORDER BY ... simply whether that query works without error and returns the correct rows.

If it does work, see whether this RIGHT JOIN returns the remaining rows you need.

SELECT *
FROM
    (tbl_Vendors AS vend
    INNER JOIN tbl_Inventory AS inv
    ON vend.vid = inv.VendorID)
    RIGHT JOIN tbl_ItemHistory AS hist
    ON inv.ItemNum = hist.ItemNum
WHERE inv.ItemNum Is Null;

You may need to change the WHERE clause; that was untested air code. But if that also works, combine the 2 queries into one:

SELECT *
FROM
    (tbl_Vendors AS vend
    INNER JOIN tbl_Inventory AS inv
    ON vend.vid = inv.VendorID)
    LEFT JOIN tbl_ItemHistory AS hist
    ON inv.ItemNum = hist.ItemNum
UNION ALL
SELECT *
FROM
    (tbl_Vendors AS vend
    INNER JOIN tbl_Inventory AS inv
    ON vend.vid = inv.VendorID)
    RIGHT JOIN tbl_ItemHistory AS hist
    ON inv.ItemNum = hist.ItemNum
WHERE inv.ItemNum Is Null;