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

ok, here's thing. have 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 thing, want query display inspectors, listed or not in ita_inspectors shops id = 1 , years id = 1. if inspector present in ita_inspectors table, show start , end dates, if not, show without dates.

note there might not ita_id in ita_inspectors table selected shop (imagine ita_inspectors table empty, wouls still need view of inspector names).

the inspectors table static data build ita_inspectors table.

i have tried query :

select * ((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 ita.shop_id = 1 , ((years.actual) = true); 

it works until add right join clause, error saying join expression not supported.

can guide me proper way of doing this?

well 1 solution split query doesn't have these conflicting joins create query e.g q1

select * (     (         ita inner join years on ita.id_year = years.id         ) left join ita_inspectors on ita.id = ita_inspectors.id_ita     )   

and create 2nd query make right join need

select inspectors.id, inspectors.inspector, q1.* inspectors left join q1 on inspectors.id = q1.id_ita;