not quite clear on linking 2 (or more) to 1

[Boze]

I'm trying to setup a database of our company items. Some items have more
than one vendor. I'm not sure how to link more than one vendor to an item
I have:

tblItem
ItemID (pk)
ItemPartNum
ItemDescrip
VendID (foreign key)
etc

tblVend
VendID (pk)
VendName
VendPhone
etc

I've seen this before with phone numbers where you can put TYPE of phone in
the table (cell, office, home, etc) but in this case all the Vendors have
the same status. We sometimes have 2 or more vendors we buy from and
combine to make ONE of our items.

I've been told to make a subform of Vendors in the master form of Items and
set the subform to continous but I don't know how to SELECT the Vendors.

Any help would be appreciated
Boze

 

[Duane Hookom]

You can remove the VendID from tblItem and create a "junction" table

tblItemVend
===========
ItemID (fk)
VendID (fk)

 

[John Vinson]

I'm trying to setup a database of our company items. Some items have more
than one vendor. I'm not sure how to link more than one vendor to an item
I have:

tblItem
ItemID (pk)
ItemPartNum
ItemDescrip
VendID (foreign key)
etc

tblVend
VendID (pk)
VendName
VendPhone
etc

I've seen this before with phone numbers where you can put TYPE of phone in
the table (cell, office, home, etc) but in this case all the Vendors have
the same status. We sometimes have 2 or more vendors we buy from and
combine to make ONE of our items.

I've been told to make a subform of Vendors in the master form of Items and
set the subform to continous but I don't know how to SELECT the Vendors.

You need ANOTHER TABLE.

You have a many (products) to many (vendors) relationship; each vendor
will sell one or more products, and each product may have one or more
vendors. Create a new table

tblItemVendors
ItemID (pk)
VendID (pk)

Base your subform on this table; use ItemID as the Master Link Field
and bind a combo box (based on tblVend) to the VendID field.

John W. Vinson[MVP]

 

[Boze]

Thanks John! It makes sense when you explain it so thoroughly. I'll give
it a go.

Boze

 

[Boze]

I'm not doing something correctly. I can choose a Vendor in the subform but
when I try to go to the field below to select a 2nd Vendor I get the
message: "You cannot add or change a record because a related record is
required in tblItems".

I'm guessing one goof is in creating the subform. I did create
tblItemVendors and set the 2 pks and made a subform based on that table.
I'm not clear on where I "use ItemID as the Master Link Field" so hopefully
that's the problem

Also I'm wondering... the many to many relationship between items and
vendors.. should I actually SEE a many to many relationship between the
tables or does tblItemVendor perform the function of making it many to many?
Right now I have 2 relationships;
tblItems (1) to tblItemVendor (many)
tblVendor (1) to tblItemVendor (many)

Thanks again for the help.