Outer Join Four Tables

[mjd1947]

Hi, I need to make an Access query that will outer join four separate tables, for example:
tblItemsA
UPC
Description
Dept
Aisle
Price
LastSold

tblItemsB
UPC
Description
Dept
Aisle
Price
LastSold

tblItemsC
UPC
Description
Dept
Aisle
Price
LastSold

tblItemsD
UPC
Description
Dept
Aisle
Price
LastSold

They should be joined on UPC, some UPCs are in all four table and some are only in one. I need a common list so that I can report on the price field.

Thanks in advance.

 

[PHV]

I need a common list
Like an UNION query of your 4 tables.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mjd1947]

Ok This worked. Thanks, I just couldn't think of it.

Now I have results with each UPC showing up multiple times depending on how many tables it is in. I would like a report with [UPC], [Price from ItemsA],[Price from ItemsB],[Price from ItemsC],[Price from ItemsD], etc.

Any tips?

Thanks

 

[PHV]

I would like a report with [UPC], [Price from ItemsA],[Price from ItemsB],[Price from ItemsC],[Price from ItemsD], etc
So, no UNION query, but LEFT JOINs ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mjd1947]

Any advice on how to set it up properly?

Thanks

 

[dhookom]

I would create a crosstab based on the union query.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

One way:

SELECT U.UPC,A.Price AS PriceA,B.Price AS PriceB,C.Price AS PriceC,D.Price AS PriceD
FROM (((((SELECT UPC FROM tblItemsA UNION SELECT UPC FROM tblItemsB
    UNION SELECT UPC FROM tblItemsC UNION SELECT UPC FROM tblItemsD) U)
LEFT JOIN tblItemsA A ON U.UPC=A.UPC)
LEFT JOIN tblItemsB B ON U.UPC=B.UPC)
LEFT JOIN tblItemsC C ON U.UPC=C.UPC)
LEFT JOIN tblItemsD D ON U.UPC=D.UPC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mjd1947]

This worked perfectly. Thanks for the help.

 

[dhookom]

mjd1947,

worked perfectly

Consider clicking the "Like this post? Star it!" to recognize PH as well as mark the thread as completed.

Duane
Hook'D on Access
MS Access MVP