How to include all records in both joined Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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".
 
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
 
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.
 
jazzii said:
Gary, what you say sounds logical but I am not quite sure. But will try
anyway. Thanks.
Click to expand...

Of course the best solution would be to NOT have separate tables for each year,
but rather have a single table that includes a field for the year. Then you can
easily query for any given year or years. A crosstab query would give you a
"column per year" output.
 
I'll echo Rick's suggestion -- keep ALL data in one table, add a field
indicating year. By the way, do not call it "year", as that is a reserved
word in Access.
 
Back
Top