How to join 2 Access tables to return ALL records from both tables

[Guest]

Newbie to posting questions!
I have two tables and have defined my relationships. In my query, I want to
return ALL data from BOTH tables. How can I do this? It seems Access is
missing that type of join.

 

[Rick B]

That is called a union query. It must be built in sql, not the query
builder. Use HELP and it will walk you through building a UNION query.

One thing to think about though, is why you have two tables with similar
data in them. While not necessarily wrong, this can often point to a poor
design. In many cases, people will create identical tables for each
year/department/building/state/etc. Normally these can be combined into one
table with a new field to indicate the specific distinction. Again, not
always the case, but something to think about when you find yourself in need
of a UNION query.

 

[Guest]

Not missing. Just hiding! You need a Union query.

Open up a new query in design view. Then go up to Query, SQL Specific and
chose Union. See the nice blank screen! You have to manually type in two SQL
statements combined with a Union All clause.

SELECT [FieldA] as FirstField, "TableA" as FromTable
FROM TableA
UNION All
SELECT [FieldB],"TableB"
FROM ClientShots

For this to work you need the same number of fields in both select
statements. It also helps if they are the same data type like number and
number; date and date. If need be you can pad out missing fields with
something like "TableB" in the bottom select statement.

There is a difference between UNION ALL and UNION. UNION ALL returns all
records where a plain UNION does not show duplicates.

UNION queries can be VERY slow if there are a lot of records in the tables
plus cause database bloat. UNION ALL can still be a little slow but not as
bad.

Re-reading your post, it's also possible that you want an Outer Join to get
records from both tables based on the join. Access doesn't really do this.
There is Left joins though.

 

[John Spencer]

Do you mean you want a FULL OUTER JOIN? That is you have two tables that
should be joined on one or more fields. You are right Access doesn't
directly support this.

To emulate a Full Outer Join, you need to union a Left outer join with a
"frustrated" Right outer join:

SELECT somefields
FROM tb1 LEFT JOIN tb2 ON tb1.f1 = tb2.fA
UNION
SELECT somefields
FROM tb1 RIGHT JOIN tb2 ON tb1.f1 = tb2.fA
WHERE tb1.f1 IS NULL

============================================================================
============================================================================
Michel Walsh (MVP) posted this example.
Here are two other alternatives:

SELECT x.id FROM x
UNION
SELECT y.id FROM y

saved as qu1. Then

SELECT x.*, y.*
FROM ( qu1 LEFT JOIN x ON qu1.id=x.id)
LEFT JOIN y ON qu1.id=y.id

will do your full outer join (on id).

Another solution is to use the union after the outer join:
SELECT x.id, y.id
FROM x LEFT JOIN y ON x.id=y.id
UNION
SELECT x.id, y.id
FROM x RIGHT JOIN y ON x.id=y.id
WHERE x.id IS NULL

You recognize the right join as finding records in x not in y, which
complete the first left outer join. Indeed, you have to NOT include those
already included by the first LEFT JOIN and an UNION ALL won't do the trick
since it is possible that the initial tables both have duplicated record (a
UNION ALL would incorrectly remove those dup). So, the WHERE x.id IS NULL is
really important, in general.

 

[Tom Ellison]

Hey, John:

Just a small suggestion. Since you have already eliminated the duplicates
with the filter, change to UNION ALL for better performance.

Tom Ellison