Relationship of tables - compatibility

[Guest]

I need to create tables which include products for purchase with cost and
vendors. I have not been able to create a relationship which will include a
vendors from which I can purchase the same product with different costs. The
different costs are very important since the purchases may be
interchangeable. In other words, if the cost is less expensive and
competitive pricing is evident or availability of product is the issue we may
switch purchase from one vendor to another. We need both vendors with
availabilty of the same product. Please Help!!

 

[Allen Browne]

So one product can come form multiple vendors.
One vendor can also supply multiple products.
This means you have a many-to-many relation between products and vendors.

You will therefore need 3 tables:
1. Vendor table, with VendorID primary key.

2. Product table, with ProductID primary key

3. VendorProduct, with these fields:
VendorID foreign key to Vendor.VendorID
ProductID foreign key to Product.ProductID
PriceEach how much this vendor charges for this product.

 

[Guest]

If you look at the Northwind sample database that comes with Access, they
have multiple vendors and products. You could look at the way they set up
tables and relationships to get an idea of how it could be done.

 

[Allen Browne]

In the Northwind sample database, a product can have only one supplier.