Full Outer Joins

[DrewConn]

I use SQL daily and I had a need to create a full outer join on a users local MS Access DB. To my surprise, I can't get a full outer join to work. The design view only allows left , right or inner joins. In the SQL view, none of the syntax such as full outer, cross, etc worked either.

Can anyone explain how to do a full outer join between two tables in Access?

Thanks.

 

[swampBoogie]

select * from a left join b
on a.c1 = b.c1
where b.c1 is null
union all
select * from a inner join b
on a.c1 = b.c1
union all
select * from a right join b
on a.c1 = b.c1
where a.c1 is null

 

[r937]

for a full outer join, you could also just combine the first two of those three selects in the union as follows...

select *
from a left join b
on a.c1 = b.c1
union all
select * from a right join b
on a.c1 = b.c1
where a.c1 is null

for a cross join, build what you want in query design view, just don't connect any tables with relationship lines, or if access automatically connects them, delete the relationship lines

if access does build an inner join, you could also just go into sql view, find the line that says

FROM table1 INNER JOIN table2
ON table1.x = table2.y

and change it to

FROM table1, table2

if you then switch back to design view, the relationship lines will be gone

voila, cross join

rudy

http://r937.com/

 

[BurnsORegan]

Has anyone come accross the Full Outer Join button being greyed out even though the join is possible. I am on a client site and have an issue that appear to be the Full Outer Join facility is permanently greyed out!

Help

Regards BurnsORegan

 

[JonFer]

thread701-750988