One table column's specific value depends on lookup to another table

[Kovenna]

I am trying to create a small database system that will produce a week's shopping list for food. I have a recipes table, then a Recipe_Lines table, then an Ingredients table and finally a Unit of Measurements table.

I have a form to "Add a Recipe". The control source for this form is the Recipes table. There is an embedded subform to add the Recipe_Lines for each Recipe. The Master/Child link is "Recipe_ID". So far so good.

However, in the subform, each Recipe Line has:
Recipe_ID
Recipe_Line_ID
Ingredient_ID
Unit of Measurement_ID

My problem: How do I get the "Unit of Measurement_ID" to be automatically populated from the Ingredients_UnitOfMeasurement (ie. a single value)? Nb. The Ingredients table already has populated single value permissible Units of Measure for each ingredient eg. Olive Oil can only be "Tbsp" (not millilitre or anything else)

Example: If my first ingredient for the Cottage pie recipe is "Olive Oil", I need the Unit of Measurement ID to be "Tbsp" (tablespoon) not some random value input by whoever is doing this and auto populate in the sub form

Many thanks for your help

 

[theDBguy]

Without seeing your table data, I would say it all depends. For example, you said you have an ingredients table with a unit of measure. Can each ingredient potentially have more than one UoM? If so, do you have another junction table for that? If each ingredient can only have one UoM, then you don't need the UoM ID in the recipe line items table, because it's implied from each ingredient. Otherwise, you can use code to assign a default value for it by looking it up from the junction table.

 

[Kovenna]

Thanks DBGuy,
No, I have set up the ingredients to have 1 UoM each, and only 1. I'm guessing I make the Recipe Line table column Ingredient_ID a "Lookup" tyype column which references the Ingredients table where RecipeLine_Ingredient_ID = Ingredients.IngreientsID something like that?

 

[The_Doc_Man]

Just be careful in setting up the UoM concept. Here is a more technical viewpoint to clarify your comment.

IF you have decided that "unit of measure" is singular for a given ingredient, then it belongs with the ingredient because you have declared it to be a "property" of the ingredient. In that case, the "property" needs to stay in the same table as the thing for which it is a property. This is a side effect of database normalization.

We don't know offhand just how experienced you are with relational databases. May I suggest that "database normalization" is a good topic to study? If you understand normalization up front, you will be less likely to make egregious design errors later. And there are TONS of articles out there. Just remember that if you are doing a general web search, look for "database normalization" - because without the "database" qualifier you might get diplomatic normalization, chemistry normalization, mathematical normalization, or several other disciplines worth of that topic.

The way you responded in your post #3, there is a possibility that you might do something unwise - if I take you literally at your word. DO NOT use a lookup field. They are - to be blunt about it - a pain in the toches once you start dealing with proper relationships. But if you know the ingredient, a DLookup of that ingredient would EASILY find the UoM field. OR if you have a query that joins ingredients with recipes, you can just drag along the UoM field from the ingredient table as part of the JOIN operation. Either of those will be correct.

Note that if you ever decided that you REALLY needed a second UoM possibility, things get a LOT more complex - but still doable.

 

[Pat Hartman]

My problem: How do I get the "Unit of Measurement_ID" to be automatically populated from the Ingredients_UnitOfMeasurement (ie. a single value)?

Assuming you have a table that defines all ingredients, sugar, flour, walnuts, etc. Each ingredient will have a most commonly used UOM but you can't ever say that only one UOM will ever be used for every ingredient. Even when flour is measured in grams, you probably use measuring spoons to specify very small amounts like 1/4 tsp. In the US we use cups to measure dry ingredients and measuring spoons for small quantities. So, you can have a DefaultUOM column. Then when you pick an ingredient to add to a recipe, you can copy the DefaultUOM to the UOM in the RecipeIngredient table and the user can override it if necessary. The most efficient way to do that is to include the DefaultUOM in your RowSource query for the Ingredient combo.

Select IngredientID, IngName, DefaultUOM
From tblIngredients
Order By IngName

Then in the Click event of the Ingredient combo, you can copy the DefaultUOM to UOM. The RowSource columns are a zero based array. My sample query has three columns. They would be referenced as Column(0), Column(1), Column(2). Therefore, the third column is Column(2)

Me.UOM = Me.cboIngredient.Column(2)

In the case of combos and listboxes the following three statements are equivalent:
Me.cboIngredient
Me.cboIngredient.Value
Me.cboIngredient.Column(0)

since the .Value property is the default for most controls, we omit the .Value property to save typing and shorten our code. No one ever references it by using the Column(0) form.