Two Vendor fields - relationships etc.

[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.

 

[Administrateur]

x

 

[Jack Cannon]

You could set the RecordSource of your form to something like this.

SELECT tblProduct.ProductID, tblVendor.VendorName AS Vendor1Name,
tblVendor_1.VendorName AS Vendor2Name
FROM (tblProduct LEFT JOIN tblVendor ON tblProduct.Vendor1ID =
tblVendor.VendorID) LEFT JOIN tblVendor AS tblVendor_1 ON
tblProduct.Vendor2ID = tblVendor_1.VendorID;

Another approach which makes more sense to me would be to use two subForms
on your main form.
Each of the subForms would be set to Vendor1 and Vendor2 respectively.

Jack Cannon