Solved

MS Access Nested Joins

Posted on 2010-08-31
3
285 Views
Last Modified: 2012-06-27
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
Comment
Question by:dchau12
3 Comments
 
LVL 16

Accepted Solution

by:
carsRST earned 250 total points
ID: 33568225
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
 

Author Comment

by:dchau12
ID: 33568682
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 250 total points
ID: 33569192
Why not do it in the Access query grid? You will get all the ( ) you need then.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Error in WHERE Clause 5 46
SQL Server Update Query Streamline 4 28
SQL Server syntax question 13 30
sql server insert 12 28
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now