Full Outer Join Question

I'm trying to do a full outer join between the tables PS and Verification. I
want to join the fields titled "Non-Empl ID" in both tables. What syntax do
I need? I've tried several with no success. I want to retain the
non-matches between both tables as well.

Thanks

Barb Reinhardt

Barb Reinhardt wrote:

I'm trying to do a full outer join between the tables PS and Verification. I
want to join the fields titled "Non-Empl ID" in both tables. What syntax do
I need? I've tried several with no success. I want to retain the
non-matches between both tables as well.


SELECT PS.*, Verification.*
FROM PS LEFT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
UNION ALL
SELECT PS.*, Verification.*
FROM PS RIGHT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
WHERE PS.[Non-Empl ID] Is Null

--
Marsh
MVP [MS Access]

Thank you so much. Do I need the [ ] around column names that have spaces
in them (much like I need ' ' in Excel for worksheets with spaces) ?

Barb Reinhardt



"Marshall Barton" wrote:

Barb Reinhardt wrote:

I'm trying to do a full outer join between the tables PS and Verification. I
want to join the fields titled "Non-Empl ID" in both tables. What syntax do
I need? I've tried several with no success. I want to retain the
non-matches between both tables as well.


SELECT PS.*, Verification.*
FROM PS LEFT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
UNION ALL
SELECT PS.*, Verification.*
FROM PS RIGHT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
WHERE PS.[Non-Empl ID] Is Null

--
Marsh
MVP [MS Access]
.

Yes you need the square brackets for any field or table names that have any
characters in the names other than Letters, Numbers (with a least a leading
letter), and the underscore.

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

Barb Reinhardt wrote:
Thank you so much. Do I need the [ ] around column names that have spaces
in them (much like I need ' ' in Excel for worksheets with spaces) ?

Barb Reinhardt



"Marshall Barton" wrote:

Barb Reinhardt wrote:

I'm trying to do a full outer join between the tables PS and Verification. I
want to join the fields titled "Non-Empl ID" in both tables. What syntax do
I need? I've tried several with no success. I want to retain the
non-matches between both tables as well.

SELECT PS.*, Verification.*
FROM PS LEFT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
UNION ALL
SELECT PS.*, Verification.*
FROM PS RIGHT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
WHERE PS.[Non-Empl ID] Is Null

--
Marsh
MVP [MS Access]
.

You may need brackets if you have foreign characters in your field names and
you definitely need them if you have spaces, which is why many people
recommend that such names not be used. If your names are well-behaved, from
only the US alphabet, you do not need brackets.

Pete



"Barb Reinhardt" pÃ*Å¡e v diskusnÃ*m
pÅ™Ã*spÄ›vku ...
Thank you so much. Do I need the [ ] around column names that have
spaces
in them (much like I need ' ' in Excel for worksheets with spaces) ?

Barb Reinhardt



"Marshall Barton" wrote:

Barb Reinhardt wrote:

I'm trying to do a full outer join between the tables PS and
Verification. I
want to join the fields titled "Non-Empl ID" in both tables. What
syntax do
I need? I've tried several with no success. I want to retain the
non-matches between both tables as well.


SELECT PS.*, Verification.*
FROM PS LEFT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
UNION ALL
SELECT PS.*, Verification.*
FROM PS RIGHT JOIN Verification
ON PS.[Non-Empl ID] = Verification.[Non-Empl ID]
WHERE PS.[Non-Empl ID] Is Null

--
Marsh
MVP [MS Access]
.