Setting correct relationships

[rfalv32]

I'm new to Access and I'm trying to make a db involving 3 tables (Products, Ingredients and regulations.) Each Product has many ingredients (one to many) and many regulations (one to many.) Each ingredient is included in many different products and has many regulations.



My Product table includes

• product ID
• product name
• multiple fields of ingredients for each record of products.

(I made each ingredient field it's own combo box from the ingredients table. Is this ok?)

My Ingredients table includes

• ingredient ID
• ingredient name
• multiple fields of regulations for each record of ingredients.

(I made each regulation field it's own combo box from the regulation table. Is this ok?)

My regulations table is just a listing of regulations used for the combo box.

I know this is a very beginner question, but, how do I relate these tables correctly? Also, I want the Product table to automatically update from the ingredients table the regulatory status. (Ex: if all ingredients included in a product are approved for regulation 'A', I would like the product to automatically flag 'approved' for regulation 'A.')

I've been going around in circles for days, watching videos and reading books, I just don't seem to grasp it yet. Thank you for any help.

[June7]

Review the 'sticky' thread tutorials at http://forums.aspfree.com/microsoft-access-help-18/

One-to-many and many-to-many relationships require a 'junction' table. You will need at least two more tables. Like:

tblProdIngs
ProdID
IngID

tblProdRegs
ProdID
RegID

Products and ingredients are many-to-many. If each reg can apply to more than one product, then products and regulations are also many-to-many.

The automating you describe will require code.