I'm trying to do a simple data base where I have multiple suppliers for the same products. I want to call up a single product and have it list the suppliers I can order it from. I've read through the help topics, but don't know how to set up the tables, as samples always link a single supplier to a product. Any suggestions on how to set it up, samples or other sources to get the answer?
A Many to Many relationship always requires a third table. Try:
Products
ProductID
ProductName
<other info about the product>
Suppliers
SupplierID
CompanyName
<other needed info about the supplier>
ProductSuppliers
ProductID << link to Products
SupplierID << link to Suppliers
<any needed info about this supplier-product combination>
The user interface could have a Form based on Products, with a Subform
based on ProductSuppliers - bind the SupplierID field to a Combo Box
showing the supplier name.
