Full outer join in Access

Question

I have a query which contains FULL OUTER JOIN and I want to implement it in Access.

I know that this can be done using UNION query in access but I quite don't know how. I have read some articles but didn't understood it. This is SQL Server Query which I want to rewrite in access. Help me understand it by describing it how it works.

SELECT tblPurchaseMain.Purchase_Date, tblPurchaseDetail.Quantity, tblPurchaseDetail.Rate, 
tblProduct.Product_Name, tblProductCategory.Product_Category_Name, tblSupplier.Supplier_Name,
tblSupplier.Supplier_Address, tblSupplier.Supplier_Phone_No, tblCompany.Company_Name,
tblPurchaseDetail.Amount, tblPurchaseDetail.Discount, tblPurchaseMain.TotalAmount,
tblPurchaseMain.Service_Tax, tblPurchaseDetail.TaxableAmount, 
tblPurchaseDetail.Purchase_Main_ID, tblPurchaseDetail.VAT,
tblPurchaseMain.Purchase_Other_Charges 
FROM 
tblPurchaseDetail
INNER JOIN 
tblPurchaseMain ON tblPurchaseDetail.Purchase_Main_ID = tblPurchaseMain.Purchase_Main_ID
INNER JOIN 
tblProduct ON tblPurchaseDetail.Product_ID = tblProduct.Product_ID 
INNER JOIN 
tblProductCategory ON tblProduct.Product_Category_ID = tblProductCategory.Product_Category_ID
INNER JOIN 
tblSupplier ON tblPurchaseMain.Supplier_ID = tblSupplier.Supplier_ID 
FULL OUTER JOIN 
tblCompany ON tblPurchaseMain.Company_ID = tblCompany.Company_ID 
WHERE 
(tblPurchaseMain.Purchase_Main_ID =" & _PurchaseBillNo & ") 
AND 
(tblPurchaseMain.Company_ID = " & CompanyID & ")".

Thank you.

Answer 1

Consider using an intermediary saved query holding all INNER JOIN tables that you then use in FULL OUTER JOIN with Company table by running the union of LEFT JOIN and RIGHT JOIN.

And because, MS Access SQL maintains a tough requirement to wrap table pairings in JOIN clauses inside parentheses, some of which can be nested joins (see below), it is advised to build queries with Access' Query Designer if using the MSAccess.exe GUI program. Also, consider using table aliases for concision in syntax.

Inner Query

SELECT m.Purchase_Date, d.Quantity, d.Rate, p.Product_Name, c.Product_Category_Name,
       s.Supplier_Name, s.Supplier_Address, s.Supplier_Phone_No, m.Company_ID, d.Amount,
       d.Discount, m.TotalAmount, m.Service_Tax, d.TaxableAmount, d.Purchase_Main_ID,
       d.VAT, m.Purchase_Other_Charges
FROM (((PURCHASE_MAIN m INNER JOIN PURCHASE_DETAIL d 
        ON m.Purchase_Main_ID = d.Purchase_Main_ID) 
INNER JOIN PRODUCT p ON d.Product_ID = p.Product_ID) 
INNER JOIN PRODUCT_CATEGORY c ON p.Product_Category_ID = c.Product_Category_ID) 
INNER JOIN SUPPLIER s ON m.Supplier_ID = s.Supplier_ID;

Outer Query (parentheses are not required for only one pair of tables as seen here)

SELECT q.Purchase_Date, q.Quantity, q.Rate, q.Product_Name, q.Product_Category_Name,
       q.Supplier_Name, q.Supplier_Address, q.Supplier_Phone_No, c.Company_Name, q.Amount, 
       q.Discount, q.TotalAmount, q.Service_Tax, q.TaxableAmount, q.Purchase_Main_ID, q.VAT,
       q.Purchase_Other_Charges
FROM PRODUCT_INNERQ AS q 
LEFT JOIN COMPANY AS c ON q.Company_ID = c.Company_ID

UNION ALL

SELECT q.Purchase_Date, q.Quantity, q.Rate, q.Product_Name, q.Product_Category_Name, 
       q.Supplier_Name, q.Supplier_Address, q.Supplier_Phone_No, c.Company_Name, q.Amount, 
       q.Discount, q.TotalAmount, q.Service_Tax, q.TaxableAmount, q.Purchase_Main_ID, q.VAT, 
       q.Purchase_Other_Charges
FROM PRODUCT_INNERQ AS q 
RIGHT JOIN COMPANY AS c ON q.Company_ID = c.Company_ID
WHERE (((q.Company_ID) IS Null));

Do note: there is no diagram representation for the UNION query in MS Access. Above two sets are the SELECT statements individually run. Notice the arrow directions.

Answer 2

Review FullOuterJoin in Access However, your requirement seems much more complicated due to the number of tables involved. Perhaps need to break this down. Do a query for the Purchase, Product, and Supplier tables. Then use that query in the UNION of LEFT and RIGHT queries with Company table.