How to keep all values from two table

[jamal ZETAWI]

How to keep all the values from two tables as they are joined in a query, I
mean that the query must contain both the matched and the not matched

Thank you

 

[John W. Vinson]

On Thu, 8 Apr 2010 12:26:01 -0700, jamal ZETAWI <jamal

How to keep all the values from two tables as they are joined in a query, I
mean that the query must contain both the matched and the not matched

Thank you

Could you explain how the tables are related? An OUTER JOIN will probably
work, but it's not clear from the question. Perhaps you could post the SQL
view of your current query.

 

[Marco Pagliero]

How to keep all the values from two tables as they are joined in a query, I
mean that the query must contain both the matched and the not matched

Is this an Union query?
An Union query _must_ have all fields matched _and_ in the same order.
The only possibility I know is to put the non matching fields in the
other table too and let them empty.

Greetings
Marco P

 

[KARL DEWEY]

Try this --
SELECT Table1.*, Table2.*
FROM Table1 LEFT JOIN Table2 ON Table1.Field_To_Match = Table2.Field_To_Match;

 

[John Spencer]

Table a with one field and records with values A, B, D
Table b with one field and records with values A, B, X, Z
Join on the field

Desired results
AA
BB
D_
_X
_Z

That is known as a FULL OUTER JOIN and is not supported in Access. You can
use a UNION query to get the results that would be returned by a full outer join.

SELECT TableA.Field, TableB.Field
FROM TableA LEFT JOIN TableB
ON TableA.Field = TableB.Field
UNION ALL
SELECT TableA.Field, TableB.Field
FROM TableA RIGHT JOIN TableB
ON TableA.Field=TableB.Field
WHERE TableA.Field is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County