Returning multiple datasets from table in a form

[need_help12]

Good afternoon. Relatively new to Access, and the build I'm doing needs to be very simplistic. In this case, I'm building a simple database to store vendor data and need to be able to retrieve this information from the table in which I store it.

In this case, I've built a simple form "Vendor Search" which provides a combo-box to select the vendor you're looking for and it'll display the info accordingly. The issue I've run into is I have vendors at multiple locations, thus different data associated with each. My question is two-fold -



1. How do I remove the duplicates from the drop-down list?

2. How, if I select a vendor with multiple data sets, can I view the multiple listings, and not just the first one on the list?

I've attached my dummy database to be more clear. I appreciate any assistance anyone is willing to lend.

[AccessMSSQL]

You need a master vendors table. Your database thus far is not normalized: http://databases.about.com/od/specif...malization.htm

Create a new table called VendorMaster and add fields:
VendorID automnumber
VendorName text
URL text,
etc.

Add a field to your Vendor Data table called VendorID numeric

Create the relationship between the two - see the below diagram:

YOu will have to modify your vendor search screen combo box to select from the master vendor table. You will also have to populate the new VendorID column in your Vendor Data table with the ID in the Master table.

[June7]

1. Problem with Vendor Data table is there is more than one record for each Vendor because of multiple contacts. This is a 1 to many relationship. The contact info should be in a separate child table. Including the ID field in the combobox RowSource makes every record unique so the two Vendor1 records must list as separate rows in the list. Exclude the ID field and use DISTINCT keyword to get unique list of vendor names:
SELECT DISTINCT [Vendor Data].Vendor FROM [Vendor Data] ORDER BY [Vendor Data].Vendor;
Change combobox properties to:
ColumnCount: 1
ColumnWidths: 1"

2. Change the embedded macro to SetFilter action and the WHERE CONDITION to reference the Vendor field instead of ID: [Vendor]=[Forms]![Vendor Search]![Combo83]

[need_help12]

Thank you both for your help, this is how I'll spend the morning today. Much appreciated to you both!