How to include all records in both joined Tables

J

jazzii

Hi,

This may sound like a real stupid question but I am totally new to Access
and only using it via trial and error.

I would like to include all records in both tables. The two tables consist
of exactly the same fields. The outer join options only have all records from
either table but not both. Please help.
 
J

Jeff Boyce

You haven't indicated if/how the two tables are related.

You haven't indicated why you have two tables with "exactly the same
fields".
 
G

Gary Walter

Hi jazzii,

It sounds like you are asking
for a Full Outer Join.

SELECT A.*, B.*
FROM A LEFT JOIN B
ON A.pkA = B.pkB
UNION
SELECT A.*, B.*
FROM B LEFT JOIN A
ON B.pkB = A.pkA

You cannot graphically "design" this query,
but will have to go into "SQL View" to type
it out.

As a "totally new" user, your best strategy
might be to create each outer join separately,
verify they work, then copy one of the SQL
stmts, go into the other's SQL View,
type UNION,
then paste in copy of the other SQL.

It also might be good to look up "UNION"
in Help. If you use just "UNION", it will
make sure there are no repeated records
(which can be slow in some cases).

Using "UNION ALL" will return *all* records
from both queries, but is faster.

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
J

jazzii

Hi Jeff,

Reason is one table is for 2003 and the other is for 2004 which consist of
fields Sales Person, Volume, Profit, Revenue..etc

I would like to put the 2003 and 2004 sales volume, profit, revenue, etc
side by side for each sales person.
 
J

jazzii

Gary, what you say sounds logical but I am not quite sure. But will try
anyway. Thanks.
 
Top