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" 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" <(E-Mail Removed)> pÃ*Å¡e v diskusnÃ*m
pÅ™Ã*spÄ›vku news:28A620E5-5CBC-407D-A092-(E-Mail Removed)...
> 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]
>> .
>>