LEFT JOIN question

Good day all,

I have four tables that I am attempting to LEFT JOIN together in a
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11919-left-join-question.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11919
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

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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/...UBED1=ACCESS-L


"Access Sortof Newb" wrote in message
news:1151000444.304261.140650@m73g2000cwd.googlegr oups.com...
> 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.Par
tNumberSupplierID
>
> I attempted this SQL statement but was flagged for a syntax error:
> SELECT tblPartNumber.fldPartNumber,
> tblPartNumberVendor.fldPartNumberVendor,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11919
> tblPartNumberSupplier.fldPartNumberSupplier
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11919
> 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
>

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" wrote in message
news:1151000444.304261.140650@m73g2000cwd.googlegr oups.com...
> 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)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11919
> tblPartNumberSupplier (PartNumberSupplierID is primary key autonumber)
> tblPartNumberCost (PartNumberCostID is primary key autonumber)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11919
>
> 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
>

Thanks Rob, that fixed the problem.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11919

Rob Parker wrote:
> I suspect it's a problem with misplaced brackets. Also, there's an apparent
> typo in the second-last line (.PartNumberSupplerID should almost certainly
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11919
> 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