Related subdatasheets

[floorboy]

Hi all. I have re-designed my database. Initially I had my products over 8 tables which represented 8 product groups because the attributes for each group are different. Now I have just one tblProducts and I've built my 8 forms from queries which separate the groups. Trouble is, Ive tried to include a subdata sheet to show the suppliers of that product and their related prices but I'm not able to enter any data in it.

I have made the many to many relationship through the join table correctly as far as I can see. I'd also like to show the colours of that particular product in another subdata sheet on the form.

It's a carpet shop and there may be up to 10 suppliers for the same product and up to 30-40 colours.

I think I've done the may to many right as below

tbl Products
Product_ID PK
Supplier_ID FK
Product Number
Product Name
Etc.......

tbl Suppliers
Supplier_ID PK
Supplier Name
Address
Tel
Etc....

tbl Suppliers_Products
Product_ID FK
Supplier_ID FK

I also need to have the Supplier_Name field in the subdatasheet as a dropdown pick list which I created using a lookup table. I have to catalogue aprox 10.000 products & samples and i just cant type the potential ten suppliers of each product, It would take me years and I'm doing this in my own time to get it up and running.

Many thanks in anticipation of any help.

 

[pbaldy]

I'm not clear on what the question is, but I don't think you wasn't the supplier field in the products table. That relationship is handled by the junction table.

 

[floorboy]

Hi Paul thanks for the reply.

Well basically I have made a form with all the product data at the top from the Products table and a subform (datasheet view) pulling across the supplier field along with supplier terms and price info from the supplier table but it's not doing anything, I can't enter any data it's just dead

Do you ever actually use the fields from the join table or are they just there for the database

 

[floorboy]

Also, when I do the colours for each carpet, do I put 40 colour fileds on the product table or should I create a colours table and somehow relate that