Nested Inner Join problems....

Hello All.

I have gone through quite a few of the posts here but have been unable to find a solution to my problem.

I am trying to send an SQL statement that links one table to more than seven tables. I know I need to create a nested inner join but for osme reason I am really having a difficult time getting the syntax correct.

I am developing a Visual Basic app that utilizes an Access database to track customer related information. Tracked information includes an Equipment table that has related tables defined for Model, Manufacturer, Conical, KnifeType etc...

So my equipment table needs to access the related tables (approx. 7 tables)in order to link the various fields.

This is a working version only using one inner join.

chipSQL = "SELECT DISTINCTROW tblChippingSystems.Manufacturer_ID,"
chipSQL = chipSQL & " tblChippingSystems.Chippingcounter, tblManufacturer.ManufacturerDesc, "
chipSQL = chipSQL & " tblChippingSystems.Customer_ID "
chipSQL = chipSQL & " FROM tblChippingSystems "
chipSQL = chipSQL & " INNER JOIN tblManufacturer ON tblChippingSystems.Manufacturer_ID ="
chipSQL = chipSQL & " tblManufacturer.ManufacturerI_D "
chipSQL = chipSQL & " WHERE tblChippingSystems.Customer_ID = " & _
lCurrentCustomerKey
chipSQL = chipSQL & " ORDER BY tblChippingSystems.Manufacturer_ID DESC"


Set rsChipSys = dbCustomer.OpenRecordset(chipSQL)

//end of code


Now just trying to another table creates my headache. If i add this statement i can't seem to get any nested joins to work.

Heres the statement.
chipSQL = chipSQL & " tblModel.ModelID, tblModel.ModelDesc, "

Now I just need to create a nested join that joins my Chipping System table (tblChippingSystems) to my model table(tblModel), this is where I get confused.

Any idea on how to get this nested join to work? I have seen many examples but these examples link one table thats linked to another and so one. I need to just link one table to seven tables.

This is how they would link.

tblChippingSystems.Manufacturer_ID = tblManufacturer.Manufacturer_ID

tblChippingSystems.Model_ID = tblModel.Model_ID

etc.....

Sorry for the long post, any help would be greatly appreciated.

any help? okay, first advice, run your queries in access directly, and only after you get them working, convert them to VB

here's how to add that third table:
notice how the first join has parentheses around it -- i think that's an access requirement

i'm fairly sure you don't need DISTINCTROW

rudy
http://r937.com/

Although your SQL statement does work, (syntactically)it does not get the recordcount from the table (tblChippingSYstems) anymore. There is one record and it is saying 0 records.

I will try to see what I can do with it.

Thank you Rudy.

well, it could be that "k" key value

you have to put the customer_id value instead of the k

rudy

I only rewrote the INNER JOINS, and left the WHERE and ORDER statement *** they were.

well, okay

if you want any further help, you'll ahve to show your revised query

hey, did you notice how your typo (an extra "s" on the word "as")got turned into asterisks? this forum has an obscenity filter!

Yes I noticed my typo,,thats funny.

Here is the revised query.

chipSQL = "SELECT DISTINCTROW tblChippingSystems.ManufacturerID, "

chipSQL = chipSQL & " tblChippingSystems.Chippingcounter, tblManufacturer.ManufacturerDesc, "

chipSQL = chipSQL & " tblModel.ModelID, tblModel.ModelDesc, "

chipSQL = chipSQL & " tblChippingSystems.ChippingNotes, "

chipSQL = chipSQL & " tblChippingSystems.CustomerID "

chipSQL = chipSQL & " FROM (tblChippingSystems INNER JOIN tblManufacturer ON tblChippingSystems.Manufacturer_ID = tblManufacturer.Manufacturer_ID ) INNER JOIN tblModel ON tblChippingSystems.Model_ID = tblModel.Model_ID WHERE tblChippingSystems.Customer_ID = lCurrentCustomerKey ORDER BY tblChippingSystems.ManufacturerID DESC"

chipSQL = chipSQL & " WHERE tblChippingSystems.CustomerID = " & _
lCurrentCustomerKey
chipSQL = chipSQL & " ORDER BY tblChippingSystems.ManufacturerID DESC"

Set rsChipSys = dbCustomer.OpenRecordset(chipSQL)


This code generates an error. (Too few parameters - 6)

Still trying to figure it out.

looks to me like you have two WHERE clauses and two ORDER BYs

when in doubt, print the sql out

if you're going to use VB or whatever to generate the sql, print it out first and run it by itself

Excellent eyes Rudy.

Yes I caught that and fixed the other problem as well. I was referring to my some table fields as field_ID when they should have been fieldID. Once that was fixed now I am down to this error.

Run-Time '3061'
Too Few Parameters. Expected 1

Getting closer anyway.

WEll Rudy this is what the final code ended up looking like after all that, heres the snippet.

equipSQL = "SELECT DISTINCTROW tblEquipment.CustomerID, tblEquipment.ManufacturerID, "
equipSQL = equipSQL & " tblEquipment.SizeID, tblEquipment.RotorID, "
equipSQL = equipSQL & " tblEquipment.ArmTypeID, tblEquipment.TipTypeID, "
equipSQL = equipSQL & " tblEquipment.TensionID, tblEquipment.RotationID, "
equipSQL = equipSQL & " tblEquipment.AnvilTypeID, tblEquipment.KnifeSystemID, "
equipSQL = equipSQL & " tblEquipment.EquipmentNotes, tblEquipment.EquipmentCounter, "
equipSQL = equipSQL & " tblEquipment.EquipmentTypeID, tblManufacturer.ManufacturerDesc, "
equipSQL = equipSQL & " tblSize.SizeDesc, tblRotors.RotorDesc, "
equipSQL = equipSQL & " tblArmType.ArmTypeDesc, tblTipType.TipTypeDesc, "
equipSQL = equipSQL & " tblTension.TensionDesc, tblRotation.RotationDesc, "
equipSQL = equipSQL & " tblAnvilType.AnvilTypeDesc, tblKnifeSystem.KnifeSystemDesc, "
equipSQL = equipSQL & " tblEquipmentType.EquipmentTypeDesc "
equipSQL = equipSQL & " FROM (((((((((tblEquipment INNER JOIN tblManufacturer ON tblEquipment.ManufacturerID = tblManufacturer.ManufacturerID) INNER JOIN tblSize ON tblEquipment.SizeID = tblSize.SizeID) INNER JOIN tblRotors ON tblEquipment.RotorID = tblRotors.RotorID) INNER JOIN tblArmType ON tblEquipment.ArmTypeID = tblArmType.ArmTypeID) INNER JOIN tblTipType ON tblEquipment.TipTypeID = tblTipType.TipTypeID) INNER JOIN tblTension ON tblEquipment.TensionID = tblTension.TensionID) INNER JOIN tblRotation ON tblEquipment.RotationID = tblRotation.RotationID) INNER JOIN tblAnvilType ON tblEquipment.AnvilTypeID = tblAnvilType.AnvilTypeID) INNER JOIN tblKnifeSystem ON tblEquipment.KnifeSystemID = tblKnifeSystem.KnifeSystemID) INNER JOIN tblEquipmentType ON tblEquipment.EquipmentTypeID = tblEquipmentType.EquipmentTypeID "

equipSQL = equipSQL & " WHERE tblEquipment.CustomerID = " & _
lCurrentCustomerKey
equipSQL = equipSQL & " ORDER BY tblEquipment.EquipmentTypeID DESC"



Set rsEquip = dbCustomer.OpenRecordset(equipSQL)

Finally got it too work and I think your solution would have worked too and got me in the right direction. Firstly not having any information in some tables obviously did not help. After that was said and done, I reworked the query in Access and got the code working.

Thanks again for your input and help.