MS Access multi (INNER, LEFT & RIGHT) JOIN query

Question

Ok, here's the thing. I have the Following tables involved :

> YEARS
------------------------------ 
ID    YEAR      ACTUAL
------------------------------ 
1     2014-15   TRUE 
2     2015-16   FALSE


> SHOPS
------------------------------ 
ID    NAME     ...
------------------------------ 
1     ThisShop ...


> ITA
------------------------------ 
ID    YEAR_ID   SHOP_ID
------------------------------ 
1     1         1 
2     1         2 
...


> INSPECTORS
------------------------------ 
ID    INSPECTOR
------------------------------ 
1     M. Black
2     M. White
3     M. Brown
...


> ITA_INSPECTORS
-------------------------------------------------------
ID    ID_ITA    ID_INSPCTR     StartDate    EndDate
-------------------------------------------------------

Here's the thing, I want a query to display ALL the INSPECTORS, listed or not in ITA_INSPECTORS for the SHOPS ID = 1 AND YEARS ID = 1. If the inspector is present in the ITA_INSPECTORS table, show the Start and End dates, if not, show without the dates.

Note that there might not be an ITA_ID in the ITA_INSPECTORS table for a selected shop (imagine the ITA_INSPECTORS table is empty, I wouls still need to view all of the INSPECTOR names).

The INSPECTORS table is static data to build the ITA_INSPECTORS table.

I have tried this query :

SELECT * FROM ((ITA 
INNER JOIN YEARS ON ITA.ID_YEAR = YEARS.ID)
LEFT JOIN ITA_INSPECTORS ON ITA.ID = ITA_INSPECTORS.ID_ITA)
RIGHT JOIN INSPECTORS ON ITA_INSPECTORS.ID_INSPCTR = INSPECTORS.ID
WHERE ITA.SHOP_ID = 1 AND ((YEARS.ACTUAL) = True);

It works until I add the RIGHT JOIN clause, then I get an error saying Join expression not supported.

Can anybody guide me to the proper way of doing this?

Answer 1

Well one solution is to split the query so that it doesn't have these conflicting joins So create a query e.g q1

SELECT *
FROM (
    (
        ITA INNER JOIN YEARS ON ITA.ID_YEAR = YEARS.ID
        ) LEFT JOIN ITA_INSPECTORS ON ITA.ID = ITA_INSPECTORS.ID_ITA
    )  

and then create a 2nd query to make the right join you need

SELECT INSPECTORS.ID, INSPECTORS.INSPECTOR, q1.*
FROM INSPECTORS LEFT JOIN q1 ON INSPECTORS.ID = q1.ID_ITA;