If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Full Outer Join Question
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] |
#3
|
|||
|
|||
Full Outer Join Question
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] . |
#4
|
|||
|
|||
Full Outer Join Question
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] . |
#5
|
|||
|
|||
Full Outer Join Question
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] . |
Thread Tools | |
Display Modes | |
|
|