LEFT JOIN question

[Access Sortof Newb]

Good day all,

I have four tables that I am attempting to LEFT JOIN together in a
query:
tblPartNumber (PartNumberID is primary key autonumber)
tblPartNumberVendor (PartNumberVendorID is primary key autonumber)
tblPartNumberSupplier (PartNumberSupplierID is primary key autonumber)
tblPartNumberCost (PartNumberCostID is primary key autonumber)

The relationships are as follows:
tblPartNumber.PartNumberID--1-------many-->tblPartNumberVendor.PartNumberID
tblPartNumberVendor.PartNumberVendorID--1------many-->tblPartNumberSupplier.PartNumberVendorID
tblPartNumberSupplier.PartNumberSupplierID--1---many-->tblPartNumberCost.PartNumberSupplierID

I attempted this SQL statement but was flagged for a syntax error:
SELECT tblPartNumber.fldPartNumber,
tblPartNumberVendor.fldPartNumberVendor,
tblPartNumberSupplier.fldPartNumberSupplier
FROM tblPartNumber LEFT JOIN tblPartNumberVendor ON
(tblPartNumber.PartNumberID = tblPartNumberVendor.PartNumberID)
LEFT JOIN tblPartNumberSupplier ON
(tblPartNumberVendor.PartNumberVendorID =
tblPartNumberSupplier.PartNumberVendorID)
LEFT JOIN tblPartNumberCost ON
(tblPartNumberSupplier.PartNumberSupplerID =
tblPartNumberCost.PartNumberSupplierID);

Should I change my relationships and have PartNumberID be related
throughout all four tables?

Thanks for the help

 

[Roger Carlson]

Not if your relationships are as you describe them. What is the error?


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Rob Parker]

I suspect it's a problem with misplaced brackets. Also, there's an apparent
typo in the second-last line (.PartNumberSupplerID should almost certainly
be .PartNumberSupplierID - changed below). See if this gives what you want:

SELECT tblPartNumber.fldPartNumber,
tblPartNumberVendor.fldPartNumberVendor,
tblPartNumberSupplier.fldPartNumberSupplier
FROM ((tblPartNumber LEFT JOIN tblPartNumberVendor ON
tblPartNumber.PartNumberID = tblPartNumberVendor.PartNumberID)
LEFT JOIN tblPartNumberSupplier ON
tblPartNumberVendor.PartNumberVendorID =
tblPartNumberSupplier.PartNumberVendorID)
LEFT JOIN tblPartNumberCost ON
tblPartNumberSupplier.PartNumberSupplierID =
tblPartNumberCost.PartNumberSupplierID;

HTH,

Rob

 

[Access Sortof Newb]

Thanks Rob, that fixed the problem.