Can Access to "FULL OUTER JOINS"?

C

Conan Kelly

Hello all,

In SQL Server, I can do FULL OUTER JOINS where I can return all records from both tables/queries. It doesn't appear that Access is
capable of doing this. Can this be done?

TIA,

Conan Kelly
 
C

Conan Kelly

Gary,

Thank you for your feedback.

What you suggested will not accomplish what I'm trying to do. I don't believe how I'm using the FULL OUTER JOINS is a design flaw.

What I use them for is when we do updates, I like to take the "ProductCode" field/column from the exsisting data and same
field/column in the new data (before importing into the main table), line them up next to each other, and do a FULL OUTER JOIN on
them so I can see all the records in both tables (including NULLS in the old data and in the new data). That way I can see what
product codes are new and what product codes have been discontinued.

With your UNION query, I will get all product codes in one column/field. It won't be in the format that I want.

Thanks again for all of your help,

Conan
 
C

Conan Kelly

Ah-haaaaa!!!!

I see what you are getting at now. Yeah, that should work.

Thanks again for all of your help,

Conan Kelly
 
M

Michel Walsh

Hi,


If you have a table with all ProductCode, old and new:


SELECT ProductCode FROM tableOld
UNION
SELECT ProductCode FROM tableNew


Then you would not need full outer join, just simple outer join. Let the
previous query be a table (so we can index its unique field)


SELECT whatever
FROM (previousTable As a LEFT JOIN tableOld as o ON a.productCode =
o.productCode)
LEFT JOIN tableNew As n ON a.productCode = n.productCode


Furthermore, keeping that table with all the possible ProductCode is
probably a good idea, since it is likely it would be involved in other
queries too. Sure, you can also keep it as a UNION query, but then, it is
not indexed.


Vanderghast, Access MVP
 
Top