Building a better mouse trap :)

[ldodge]

I will have to play with that a bit as to how to have a look up table with only select records from the original talble. Thanks again.
LCD

[jzwp11]

As a caution, you should not use combo/list boxes at the table level. They are best used on forms. This site has more details why lookups at the table level are not a good idea. You could add a field in tblDefinitions I would guess that would allow you to distinguish one group of flavors from another. You would then use a query to restrict the list to 1 group or the other.

[ldodge]

great link

[ldodge]

How do I make a list box at the Form level that will enable me to pick multiple items in different orders.
i.e.
Form A
1,2,3,4
Form B
2,4,3

[jzwp11]

I'm not sure I understand what you are after? I think what you are after is a form/subform design and you would create a record (in the main form) and the subform would hold related records. If you could post an example of what you are trying to model that would help. If you could also post your database (with any sensitive data removed/changed) we can see what you have & attempt to duplicate what you are trying to model.

[ldodge]

What i am trying to do is build a recipe book. I have a list of ingredients ie milk, cream, sugar, food starch, etc (probably about 75 - 100 ingredients total). Currently I just have a field that I manually typed the list of ingredients in decending order (we have to please the FDA you know ). I am seeing the shortcoming of doing it this way and would like to have something like a pull down menu that I can pick the ingredients as they show up on the label. I would send a version of the database, but my network is down right now. If you have any other questions, let me know. Thanks

[jzwp11]

You would put all the ingredients in a table with each ingredient as a record. Since a recipe has many ingredients and an ingredient can be used in many recipes you have a many-to-many relationship, so you will need the following tables.

tblRecipe
-pkRecipeID primary key autonumber
-txtRecipeName

tblIngredients
-pkIngID primary key, autonumber
-txtIngredientName

tblRecipeIngredients
-pkRecIngID primary key, autonumber
-fkRecipeID foreign key to tblRecipe
-fkIngID foreign key to tblIngredients
-lngQTY (quantity field for the ingredient in the particular recipe)
-fkUOMID foreign key to tblUnitsOfMeasure (a field to describe the units of measure for the quantity needed)

tblUnitsOfMeasure (a record for each ie. gallons, pounds, liters, kg etc.)
-pkUOMID primary key, autonumber
-txtUOM


In terms of forms, you would have your main form based on tblRecipe with a subform (in datasheet view) based on tblRecipeIngredients and within that subform you would use a combo box that is based on tblIngredients.

[ldodge]

Thank you -- and as far as entering things from the combo box, how will the sequence be controlled?

[jzwp11]

Since the ways things are entered cannot be fully controlled via the primary key field it would be best to have a sequence number field that would provide the sequence of the ingredients. You can then use this field to order the data (after entry)

tblRecipeIngredients
-pkRecIngID primary key, autonumber
-fkRecipeID foreign key to tblRecipe
-fkIngID foreign key to tblIngredients
-lngQTY (quantity field for the ingredient in the particular recipe)
-fkUOMID foreign key to tblUnitsOfMeasure (a field to describe the units of measure for the quantity needed)
-longSeqNo

[ldodge]

whew this is going to take some work , but hey what's life w/o a challenge
Thanks again
LCD

[jzwp11]

Attached is an example database to help illustrate

[ldodge]

Wow you are incredible. Two questions -- What does fkIngID stand for (I checked the description but it was empty) and two how do you get it so you can open more than one window at a time. When I create a database, I can only open one window at a time though the tabs are at the top.

[jzwp11]

First, when I use the prefix pk, it indicates the primary key field; similarly, the fk refers to a foreign key field.

fkIngID is the foreign key field that holds the reference to the ingredient. In other words, the value in the fkIngID field will be equal to a pkIngID in tblIngredients. The primary key-->foreign key establishes the relationship between the tables.

[ldodge]

duh, I knew that it just wasn't clicking sorry

[jzwp11]

No problem!