User defines # of field instances to generate a form

[dutch245]

Afternoon everyone!

First off, please let me know if there is a more appropriate spot for this question, I will gladly move it if so.



I am new to access and I am messing around in an attempt to build a database to keep track of my home brewing hobby. One of the things I would like to do is to be able to input and store recipes. I have already made a table that has fields for the various ingredients such as water, grains, and hops. Looking at various recipes, I have found that some use one kind of grains, while others use two sometimes even three. Same goes for the hops, yeasts, all the ingredients really.

Is there a way that when a user first opens a form, a dialog box can prompt them with how many types of hops or grains they are using, then provides that many instances of each field in the report? I'm hoping there is a way, so I don't have to make separate forms for beers that have 1 grain versus 2, etc.

If my question isn't clear, please let me know and I will attempt to reiterate.

Thanks!

[ssanfu]

I have already made a table that has fields for the various ingredients such as water, grains, and hops.

This structure is not normalized; you should read up on normalization.

I would start out with these tables:

tblRecipes
----------------
RecipeID_PK     Autonumber
Recipe_Desc     Text

tblRecipeDetails
---------------
RecipeDetailID_PK Autonumber
RecipeID_FK         Long - link to tblRecipes
Ingredient_FK      Long - link to tblIngredients 
Amount              Single  
Measure_FK        Long - link to tblMeasures

tblIngredients
--------------
IngredientID_PK   Autonumber
Ingredient_Desc   Text  (Hops, Water, Sugar, ...)

tblMeasures
-------------
MeasureID_PK   Autonumber
Measure_Desc  Text  (Cup, tblspn, teaspoon, Gram, lb, ...)

There is a Recipe model at
http://databaseanswers.com/data_models/index.htm
The link wasn't working, but I sent an email, asking for the link to be fixed.

Search to forum for "normalization" (and Google). Very Important to understand.

[dutch245]

Here's how my data is broken down, I coulda sworn this was normalized hahaha

[ssanfu]

Any time you see data as field names (see red circles), the structure is not normalized - should try to be in 3NF.

(BTW, you shouldn't use dashes or spaces in field names. If you wanted to start using SQL Server or SQL Server Express, it would bomb big time. Dashes and spaces are not allowed in SQL Server names.)


Also, as far as I can tell, you don't have any linking (PK - FK) fields

[June7]

I agree the data structure does not appear normalized. Will each table have unique record per recipe and each recipe will always have one and only one record in every table? If yes, then these tables are one-to-one relationship and is essentially a single 'flat file' and could be one table of 45 fields.

There are definitely no pk/fk fields defined.

It is a balancing act between normalization and ease of data entry/output.