title: Full Join on MS Access
body: <p>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...</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>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!</p>
  
  <p>edit: 2 step joins, </p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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... </p>