cascading or repeating fields ?

Question

i am doing a recipe database. i want to be able to input each ingredient as 5 separate fields ( weight, quantity, etc. ) but each recipe has a different number of ingredients. eg. If there were 8 ingredients, that would be 40 fields.

can i set it up so when i enter the first ingredient, a new field opens for the next ingredient, and so on. this would also apply to the form so i don't have a bunch of blank lines for ingredients.

Answer 1

There is a recipes template for Access. I would suggest using that as a starting point. Tom is 100% correct, You need multiple tables. A table for the details of the recipe and another table for details about the ingredients. So each ingredient represent a record (row) in the Ingredients table for that recipe. This is a process called normalization and its how relational databases are designed. I suggest doing some research on normalization before you proceed.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Answer 2

> If there were 8 ingredients, that would be 40 fields

No! That would be 8 rows.

Your main tables would constitute a classic many-to-many relation: each recipe has many ingredients, and each ingredient occurs in many recipes.

tblRecipes

RecipeID autonumber required PK

RecipeName text(100) required UniqueIndex

...other fields...

tblIngredients

IngredientID autonumber required PK

IngredientName text(100) required UniqueIndex

UnitOfMeasure - probably a foreign key value linked to tblUnitOfMeasures

...other fields...

tblIngredientsInRecipe

RecipeID long int required PK

IngredientID long int required PK

Quantity single required  'In the unit of measure given by tblIngredients

Now you can build a mainform on tblRecipes with a subform to enter the ingredients, one row at a time. Compare this with how in the Northwind sample application you have tblOrders=tblRecipes, tblOrderDetails=tblIngredientsInRecipe, and tblProducts=tblIngredients. frmOrders implements how to update the first two tables.

---

-Tom. Microsoft Access MVP