SQL query to see data from several instances of a linked table 1

[dverdi]

Hello,
I am normally an embedded programmer, but I now need to write an inventory-type Access application. I was doing OK until I tried to make a Form to browse the Parts table.
A quick search of this site's archives does not present any obvious solutions.

The problem comes down to 3 tables - Parts, Types and Suppliers.

Each record in Parts includes the fields TypeID that points to one record in Type, and SuppID1, SuppID2 and SuppID3 that point to separate records in Suppliers.

Each record in Types has a Name and Description.

Each record in Suppliers has several address fields and other contact details.

I am trying to make a Form that allows the user to browse through all Parts, and for each Part display also the name and description of the associated Type, and also display the address information for each of the 3 associated Suppliers.

I can get the Type information easily enough by giving the Form the RecordSource
"SELECT [Parts].*, [Types].* FROM [Parts] INNER JOIN [Types] ON [Parts].TypeID=[Type].TypeID"

How do I edit this statement to include the Suppliers data?
Help appreciated, as I am feeling very stupid.

 

[ESquared]

Don't feel stupid, dverdi, this is complicated stuff.

For starters, you should create a new query, go to SQL view, and paste in your SQL above. Then switch to design view and take a look. Try adding and removing things and switching back and forth from design view to learn how it affects the SQL. I use this method all the time when I am uncertain as to the syntax of something. I'm getting to the point where I don't need to do it any more, but it's always there for me to refer to. (What exactly was the syntax for deleting records from a table, again...)

As for your specific question. Now that I actually try to come up with the answer (grin) I see that the main form probably doesn't need to have the supplier data queried. Instead, for each SuppID create a combobox. In the combo box's Row Source you'd put something like "SELECT SuppID, SuppName FROM Suppliers" and set the Control Source to SuppID1 (or whatever). Create a textbox, enabled=false, locked=true, and Set the Control Source to

=DLookup("Address","Suppliers","SuppID="&[SuppID1]) & vbcrlf & DLookup("City","Suppliers","SuppID="&[SuppID1])

...

Then in the combobox's AfterUpdate event you'd do a Me.Refresh to make this textbox update when the supplier was changed.

If you are sure there will never be more than 3 suppliers, you're fine. If there could ever be several more suppliers, then you have a problem, and need to redesign your database to allow any number of them (so you don't get the silly SuppID9 SuppID10 etc.). You would create a partToSupplier Table with just 2 fields, PartID and SuppID. You would create an extremely tiny continuous form to display this table with only one combobox in the detail section (nearly exactly as listed above), and then put it into your main form as a subform and filter it so it only lists records where the PartID matched. Not sure if this would require a filter or if it could be done simply through LinkMasterFields and LinkChildFields.

I realize what I just described sounds incredibly complicated, and to some extent it is, but Many-To-Many relationships are one of the hardest database design tasks. Years down the road you will thank yourself for allowing for more suppliers per part without having to redesign each time the number increases. I admit I gave a brief overview and certainly did not describe every detail.

I hope this helps!

 

[dverdi]

Yes indeed, this has helped.
Don't quite understand it all, but the DLookUp() function did the trick.
Thanks