Simple query help

[Subhunter]

For the life of me I cannot get my query correct to pull the data I need.

Please see attachment.

Here's the scenario. We have 1 product, in turn has many parts, those parts have several distributors who supply those parts. We need to send RFQ's to those suppliers who handle the parts. I need to pull the Supplier listed for the ProductName and list all PartNumbers that Supplier can supply.

i.e. Product ThingAMajigger has 5 parts.

1st Report

TTI Inc.
addy, etc


UnitsOnOrder PartNumber Description
100 ABC
50 DEF



2nd Report

Company YYZ
addy



UnitsOnOrder PartNumber
100 ABC
50 DEF
25 MNO



3rd Report

Company BVD
addy

UnitsOnOrder PartNumber
300 UVW
250 XYZ

Any suggestions would be very helpful. I just cannot seem to get my brain in gear this week.

Thanks in advance.

Jeff

[ConneXionLost]

Hope this gets you started:

SELECT Products.ProductName, Suppliers.CompanyName, Parts.PartNumber
FROM Suppliers INNER JOIN (Products INNER JOIN (Parts INNER JOIN PartsDetails ON Parts.PartID = PartsDetails.PartID) ON Products.ProductID = Parts.ProductID) ON Suppliers.SupplierID = PartsDetails.SupplierID
GROUP BY Products.ProductName, Suppliers.CompanyName, Parts.PartNumber;

Cheers,

[Subhunter]

Thank you for your input but that does not help me right now. I am just getting back in to doing this type of programming and I am a little more rusty than I thought. Any way you could show me the design view of that code? I really appreciate the help.

Jeff

[weekend00]

Hi Subhunter,
did you mean that you have only one table for all information including supplier,procduct and parts?

[Subhunter]

The attachment has a picture of the relationships between the four tables. For one product I need to print out the RFQ's for each of the parts associated with that product. For the life of me I cannot get my brain in gear to figure it out. Thanks for the help.

[weekend00]

you follow these step to create a query with ConnextionLost's code:
1 Create query in design view, close the "select table" pop up window
2 from the menu, select View->SQL view
3 paste Connextionlost's code
4 save the query

[Subhunter]

Did that but I am getting an error message.

SELECT Products.ProductName, Suppliers.CompanyName, Parts.PartNumber
FROM Suppliers INNER JOIN (Products INNER JOIN (Parts INNER JOIN Parts Details ON Parts.PartsID = Parts Details.PartsID) ON Products.ProductID = Parts.ProductID) ON Suppliers.SupplierID = Parts Details.SupplierID
GROUP BY Products.ProductName, Suppliers.CompanyName, Parts.PartNumber;

Error states: Sytax error (missing operator) in query expression 'Parts.PartsID = Parts Details.PartsID'.

Thanks,

Jeff

[weekend00]

you need [] around all [part details]:
SELECT Products.ProductName, Suppliers.CompanyName, Parts.PartNumber
FROM Suppliers INNER JOIN (Products INNER JOIN (Parts INNER JOIN [Parts Details] ON Parts.PartsID = [Parts Details].PartsID) ON Products.ProductID = Parts.ProductID) ON Suppliers.SupplierID = [Parts Details].SupplierID
GROUP BY Products.ProductName, Suppliers.CompanyName, Parts.PartNumber;

[ConneXionLost]

Additionally, you appear to have different spelling for your [PartsID] key.

In your [Parts] table, it is [PartsID]; but in your [Parts Details] table, it is [PartID].

In my previous example, I removed the "s".

[Subhunter]

LOL, thx. I did catch that and fixed my tables accordingly so everything matches up and the code I sent back was the corrected copy.