please help urgent...thanks

[dadas]

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!

[orange]

THere is a data model at this iste that might help
http://www.databaseanswers.org/data_...ders/index.htm

[rpeare]

Are you using a multi value field in your table? If you are, don't, it's going to cause you no end of aggravation and makes the type of thing you're talking about much more difficult than it needs to be.

[dadas]

ok thatnks for the heads up. how would u suggest i do this then? i want to be able to select multiple products from the product table and then click a vendor to associate withe each one

[rpeare]

any one to many relationship is going to require two tables

1 that stores the basic information (for instance in a purchase order situation you would have the purchase order number, the vendor, the order date, etc) this field should have a unique key field (autonumber is fine)
1 that stores the detail this table would have to have its' own unique key field (again autonumber works fine) and the unique key from the basic information table as the foreign key. Then you would have the item ordered. Every item you enter in the detail would logically then, have been ordered from the vendor in the 'main' table.

I don't know exactly what's going on in your situation, it should be irrelevant to your customers who the supplier is but this is the basic idea you would follow.

One to many relationships require 2 tables not 1.