• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

MS Access Nested Joins

From the query below, I need to join another table, ZipCode, that joins to the Ozips table where Left(ZipCode.zip,3) = Ozips.Ozip. Could someone assist?

SELECT Perishible.Mode
, Ozips.Orgn
, Ozips.Ozip
, Perishible.[Origin State Prov]
, Perishible.[Destination Zone Code]
, Warehouse.Nbr
, Warehouse.Location_City
, Warehouse.State
, Warehouse.[3Digit]
FROM Warehouse
INNER JOIN (Dzips
INNER JOIN (Ozips
RIGHT JOIN Perishible
ON Ozips.Orgn = Perishible.[Origin Zone Code])
ON Dzips.Drgn = Perishible.[Destination Zone Code])
ON Warehouse.[3Digit] = Dzips.[3Digit]
GROUP BY Perishible.Mode
, Ozips.Orgn
, Ozips.Ozip
, Perishible.[Origin State Prov]
, Perishible.[Destination Zone Code]
, Warehouse.Nbr
, Warehouse.Location_City
, Warehouse.State
, Warehouse.[3Digit]

0
dchau12
Asked:
dchau12
2 Solutions
 
carsRSTCommented:
SELECT Perishible.Mode
, Ozips.Orgn
, Ozips.Ozip
, Perishible.[Origin State Prov]
, Perishible.[Destination Zone Code]
, Warehouse.Nbr
, Warehouse.Location_City
, Warehouse.State
, Warehouse.[3Digit]
FROM Warehouse
INNER JOIN (Dzips
INNER JOIN (Ozips
RIGHT JOIN Perishible
ON Ozips.Orgn = Perishible.[Origin Zone Code])
ON Dzips.Drgn = Perishible.[Destination Zone Code])
ON Warehouse.[3Digit] = Dzips.[3Digit]

Inner join ZipCode z on
Left(z.zip,3) = Ozips.Ozip
GROUP BY Perishible.Mode
, Ozips.Orgn
, Ozips.Ozip
, Perishible.[Origin State Prov]
, Perishible.[Destination Zone Code]
, Warehouse.Nbr
, Warehouse.Location_City
, Warehouse.State
, Warehouse.[3Digit]
0
 
dchau12Author Commented:
Appreciate the quick response.

There's a missing operater here:

Warehouse.[3Digit] = Dzips.[3Digit]
Inner join ZipCode z on
Left(z.zip,3) = Ozips.Ozip

Should there be a set of parenthesis somewhere?

I tried moving the INNER JOIN on ZipCode to be nested in Ozips, but to no success.
0
 
peter57rCommented:
Why not do it in the Access query grid? You will get all the ( ) you need then.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now