Multiple Vendors for products - Table relationships

[dhstein]

I have a product table that lists Vendor1 and Vendor2 (Primary and
Secondary). Each of these values is a number that corresponds to an item in
the Vendor table.
Example:

Product Table:

Prod ID- - Vendor1 - - Vendor2
1 - 37 - 46

Vendor Table:

Vendor ID - Vendor Name - Vendor Address
37 - ABC Co. - 123 Main St
46 - XYZ Co. - 456 South Street


I have a one-to-many relationship defined for the Vendor1 field and the
VendorID field in the Vendor Table. So all of my queries show the Vendor
Name for the Primary Vendor only. So far this is OK. Now I'm creating a
form to edit the product table. I show the fields Vendor 1, Vendor 2, and
Vendor Name (from the Vendor table). As a first step, I'd like to be able to
display the Vendor name that corresponds to the Vendor 2 field, but the field
"Vendor Name" is only linked to the Primary field so I can only show ABC Co.
on the form. I think I may need to set up another relationship, but I'm not
sure. I hope this isn't too confusing and thanks for any help you can
provide.

 

[tina]

you have a many-to-many relationship between vendors and products: one
vendor may supply many products, AND one product may be supplied by many
vendors. to model a many-to-many relationship, you need three tables, as

tblVendors
VendorID (pk)
VendorName
<other fields that describe a vendor only, nothing about any products they
supply>

tblProducts
ProductID (pk)
ProductName
<other fields that describe a product only, nothing about the vendors who
supply them>

tblProductVendors
ProductVendorID (pk)
ProductID (foreign key from tblProducts)
VendorID (fk from tblVendors)
<other fields that describe a specific product supplied by a specific
vendor>
(note: you could forego the ProductVendorID field, and instead use the two
foreign key fields as a combination primary key. i personally rarely use
multi-field primary keys, so i included the fields i would create in my own
database.)

the relationships are
tblProducts.ProductID 1:n tblProductVendors.ProductID
tblVendors.VendorID 1:n tblProductVendors.VendorID

hth

 

[dhstein]

Tina,

Thanks for your reply on this. I need to maintain the concept of a
primary vendor and a secondary vendor. Can that be accomplished with the
structure you showed?

 

[Tom Wickerath]

Hi dh,

I would try adding a PrimaryVendor field (long integer) to the Products
table. You can include this field in the row source for a combo box on the
subform used to select a product. Use the AfterUpdate event procedure of the
Products combo box to populate the VendorID field in the tblProductVendors
table. You'll need to reference the column number, which is zero based in VBA
code. Something like this (without error handling shown):

Private Sub cboProducts_AfterUpdate()

Me.VendorID = Me.cboProducts.Column(2)

End Sub

Column 0 likely corresponds to the ProductID field, Column 1 would be the
product itself, and Column 2 would be the PrimaryVendorID long integer. The
rowsource for the combo box would be something like this:

SELECT ProductID, ProductName, PrimaryVendor FROM tblProducts
ORDER BY ProductName

The bound column for the combo box would be column 1, which in the Property
Sheet, is *not* zero-based. Thus Column 1 in the property sheet would
correspond to the ProductID. Set the number of columns to 3, and the column
widths to 0";1.5"; 0". In addition, you can create a relationship with
enforced referential integrity between the VendorID in tblVendors and the
PrimaryVendorID in tblProducts. This will help you ensure that you do not
have a PrimaryVendor that is not present in your tblVendors table.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[tina]

that falls into the category of
<other fields that describe a specific product supplied by a specific
vendor>
so you could add a field to tblProductVendors to indicate priority level,
for example

tblProductVendors
ProductVendorID (pk)
ProductID (foreign key from tblProducts)
VendorID (fk from tblVendors)
Priority (1 for primary, 2 for secondary; this allows for assigning priorty
to any number of vendors of a product, 3 for third, 4 for fourth, etc, as
needed.)

hth