UNION JOIN and MS-ACCESS -

really confusing is, works under jet, not working under access....

how inner join union

select field1, field2 (select field1, field2, field3 table1 join table3 on table1.id=table3.id union select field1, field2, field3 table2  join table4 on table2.id=table4.id)  field3=1 

the result:

3131 --"syntax error in clause"

i reduced into:

select field1 (select field1, field3 table1 union  select field1, field3 table2) field3=1 

but still not working

even this:

select * (select field1 table1  union select field1 table2) 

doesn't work

select field1 table1 union select field1 table2 - works....

you need add field3 in query

select t.field1, t.field2  (     select field1, field2, field3     table1      union      select field1, field2, field3 table2 ) t t.field3=1 

based on edit new query need following:

select x.field1, x.field2  (     select t1.field1, t1.field2, t1.field3      table1 t1     inner join table3 t3 on t1.id=t3.id      union      select t2.field1, t2.field2, t2.field3      table2 t2     inner join table4 t4 on t2.id=t4.id  ) x x.field3=1