Calculating Cost

[chadallen]

I am creating a database for a baking business. I have already setup the tables and most of the forms but I am running into troubles when calculating the costs. What I want it to do is when we enter in a recipe for it to give us the total cost to make that recipe. Here is my table layout:
tblMeasure
MeasureID
MeasureName

tblIngredients
IngredientID
IngredientName
Amount
MeasureID
IngredientCost

tblRecipeIngredients
RecipeIngredientsID
RecipeID
IngredientID
IngredientAmount
MeasureID

tblRecipe
RecipeID
RecipeName
RecipeInstructions
Time to Prepare
Number of Servings

I have three forms for this so far. One is a simple form to enter in Measurement names (tablespoon, teaspoon, cup, etc.) Another is to enter in the Ingredients and their cost per measurement. The third is the main form, the recipe form. Here I have it setup to enter the recipe name, number of servings, instructions, etc. I have a subform for the ingredients. In the subform there is a combo box that lists the ingredient name(which is pulled form tblIngredients), cost, and measurement. Next to that is a field where we enter the amount of that ingredient we will use (this is called IngredientAmount from tblRecipeIngredient). Then I have an unbound textbox called txtCost and finally a textbox called Ingredient Cost where the total cost of that Ingredient will be placed. This is where I am having trouble. I was told to enter the following code in the data source of Ingredient Cost: =[txtCost]*[IngredientAmount]
Then in the combo box I am to enter the following code into After Update Event: Me!txtCost=Me!IngredientCost.Column(2)
But when I do this both text boxes are still empty. There is no error in the boxes they are just empty. Is there something else I need to do? If you have any suggestions I would be very grateful. If you need me to explain anything else please let me know.

thanks.

Chad

 

[Pat Hartman]

The calculations should be done in the query and they will not be stored.

Select R.RecipeIngredientsID, R.RecipeID, R.IngredientID, R.IngredientAmount, R.MeasureID, I.IngredientName, I.MeasureID, I.IngredientCost, R.IngredientAmount * I.IngredientCost As Cost
From tblRecipeIngredients as R Inner Join tblIngredients as I ON R.IngredientID = I.IngredientID;

 

[chadallen]

I should have mentioned that I am new to Access. You may have to explain what you just said a little more, sorry.

thanks

 

[The_Doc_Man]

I am slightly bothered by these two tables...

tblIngredients
IngredientID
IngredientName
Amount
MeasureID
IngredientCost

tblRecipeIngredients
RecipeIngredientsID
RecipeID
IngredientID
IngredientAmount
MeasureID

You have a redundancy on MeasureID in that both tblRecipeIngredients and tblIngredients have it. If you have the ingredient ID you ALSO have the measure ID through the ingredient table. The question would be whether you would ever have two different values for the MeasureID in the two tables (since you also have the amount in the given measure). Count this as a quibble, not a big criticism, but it is a place for confusion, and that is not good.

OK, to your cost query.

I would start with a query that joins ingredients to recipe ingredients over the ingredient ID. (Probably not a big surprise...). In this proposed query, you can have a field that is computed from other fields in the table. Call it something like "IncrementalCost" and compute the contribution of cost for that ingredient appearing in that amount in that recipe. In the query grid, it would be ugly only because you chose long names for all your fields, but it MIGHT be as easy as:

IncrementalCost: IngredientCost * ( IngredientAmount / Amount )

If you base all summation queries, reports, etc. off this query, then you can probably compute the total cost for each recipe whether in a query, a form, or a report. Now, if you make a query based on your recipe table, you can join the table to the first query I just described. From there, you could build your cookbook as a report based on the outermost query. Break on recipe ID first, order ingredients by whatever criteria you like. They would be in the detail section of the report. The instructions would be part of a header (or footer, your call) along with a field that is the total of the incremental cost column. (Which should be part of your detail section!)

Now, in the form you are talking about...

You need the PARENT form to supply the recipeID because that ties all the recipe ingredients together. It is the best link between parent and child forms, based on what you have described.

Your CHILD form needs to be based on a query because IT contains two tables - tblRecipeIngredients (where you identify the component ingredient by ID and specify each amount) and tblIngredients (where you keep the ingredient ID, unit cost per amount, units). Obviously, in this case the tblIngredients is just a lookup source - but of course, that's important, too.

So perhaps your issue is that you cannot see everything you need to see when you need to see it - and WHERE you need to see it, too.

In the CHILD form, I would put afterupdate events for ingredient and amount, such that once you have both items, you can compute the incremental cost of the line item. OR (preferred) if you have a query driving the CHILD form, compute it in the query and let Access decide when it knows enough to compute it all.

In the PARENT form, I would reference the sum of the incremental costs in the query that is the basis of your child form. This will work, you just have to be sure that you have something there to be summed. And the correct place to assure that is in the CHILD form (or its underlying query).

 

[chadallen]

I will play with that for awhile. Thanks for the help.