Linking? tables

[Colleen]

I have two tables. A Parts list and a Supplier list. While I have been able
to create a look up column to show which parts are supplied by which vendor
(and which parts the vendor supplies). I would like to be able to click on
the vendor and see all of the columns for that record in the Supplier list.
This way I could see contact info and other info that I have scanned into the
record

 

[Allen Browne]

You need a 3rd table.

Assuming your parts table has a PartID primary key, and your supplier table
has a SupplierID primary key, the 3rd table will have fields like this:
SupplierPartID AutoNumber primary key
SupplierID who supplies this part
PartID which part they supply
OrderCode the code to use to order this part from this supplier

Now create a form bound to the supplier table, and add a subform bound to
this SupplierPart table. Show the subform in Continuous Form view (or
Datasheet.) Use a combo box for the PartID (unless you have tens of
thousands of different parts.)

Now when you look up any supplier in the main form, the subform shows the
parts they supply.

If you wish you can also make a subform bound to the parts table, with a
subform bound to the SupplierPart table. When you choose any part in the
main form, it shows the suppliers in the subform.