Full Outer Join Question

  • Thread starter Thread starter Barb Reinhardt
  • Start date Start date
B

Barb Reinhardt

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
 
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 said:
Barb said:
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.
Click to expand...

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
Click to expand...
 
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 said:
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 said:
Barb said:
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.
Click to expand...
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
Click to expand...
Click to expand...
 
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 said:
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 said:
Barb said:
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.
Click to expand...

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
Click to expand...
Click to expand...
 
Back
Top