New to Access and Lost

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
Jade said:
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?
Click to expand...

Read the help sections about what a relational database is.
Look up "many to many" in help.

You have a many to many relationship where products have more than one
supplier and suppliers have more than one product.

You need a third table (and I think the Northwind database has examples).
This table only needs ProductID and SupplierID which will form a key. You
may find that a few other fields belong here.
You will have one record for each product and supplier.

Once you have this you will base most of your forms on a query involving
this table and the products or supplier table.
 
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?
Click to expand...

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.
 
Back
Top