hi everyone,
i am in the process of building a database. Its almost complete but one of the last things ive been asked to do is giving me the most trouble. I am by no means an expert at access or databases. Ive had a class on it in school and that is the extent of my knowledge besides a few other things ive taught myself.
Basically in my database i have a client table. Each client has certain products they use. I currently have a products field in my clients table with a lookup to a products table. This is done through a combo box where you can select the desired products. I have it on a form and everything is working. here is where my problem is. Now i want on my form to be able to go to a client then select a product associated with him(one of the products selected in the combo box) and have a list box display all the vendors associated with that product.

just because a productA is used by 2 different clients that doesnt mean they both use the same vendors for that product.

I hope this makes sense. And i hope its a somewhat easy thing that one of u can explain to me. THANKS!

In your database structure, you need a third table (junction table) that will associate the products with the clients. Such a table receives a copy of the primary key from both tables as foreign keys and allows the creation of many-to-many relationships.
See: Defining relationships between tables in a Microsoft Access database and Access Junction Tables

thanks so much for ur response. Can u explain this a little more. I read the links u posted and im still confused. On the form i have a multivalue combo box called Products. I want it so that when u click one of the products associated with a certain client u can then click vendors from a vendors table to associate with that product. But not the product in the products table but rather the particular instance of that product for that perticular client. THANKS SO MUCH

Please supply the actual organization of your database, I dont have enough information to provide an aswer with your description.

However, as your database is now, I don't know how (and if) it can be done. You don't explain in details the structure of the database but I fear that it is not normalized.

If the main information the db handles can be divided among Customers, Suppliers and Products, the "normal" way of working consists in creating three tables: one for data about the clients, one for data about the products and one for data about the customers (in a totally normalized database Clients and Customers would probably be stored in the same table, but let's keep things simple and allow for two tables).

To create the relationships you need two junction tables: one will maintain a many-to-many relationship between Customers and Products (a product has more than one customer and a customer buys more than one product), and a second will maintain a many-to-many relationship between Suppliers and Products (once more: a supplier can supply more than one product and some products can be supplied by more than one supplier).

Under their simplest form, these two tables consists in two columns: one receives a copy of the primary key values from one table (say Suppliers) and one copy of the primary key values from the table Products. The same process is repeated for the tables Customers and Products.

These copies of the primary key values are named foreign keys (FK). Each FK can be repeated several times in the junction table but each pair of foreing keys must be unique, each pair in the junction table is also the (composite) primary key for the table.

Let's suppose that we have two suppliers with the IDs A and B and 4 products with the IDs 1, 2, 3, 4. Supplier A sells products 1, 2 and 4 while supplier B sells products 2 and 3. The junction table creating the relationships would look like this:

The same principle is used for the relationships between Customers and Products.

You can then use Access to define the relationships:

thanks so much for ur help....just so u know, this is for a grad school project and i have very little access or database experience, so srry if this doesnt make sense, but i appreciate it. So right now i have a one to many relationship from the "ID" in the product table to the products field in the client table. products(in the client table) populates its combo box based on the product names in the product table. This combo box can have multiple variables. I need to know how in this relationship i can add the table for vendors. So that for each product a client can have multiple vendors associated with it. But a product can be used by numerous clients and those clients may ahve different vendors for the products
THANKS SO MUCH!!!

You're welcome!