How much did a collection of items cost at some date in the past?
If anyone could help me with this, I would be most appreciative.
I am learning VBA in Excel, but haven’t yet learned VBA for Access.
I have a recipe database.
Database tracks recipes, ingredients and the cost of ingredients.
Number of ounces of ingredients per recipe remains constant.
Cost per ounce of ingredients may change over time.
Question:
how to determine the cost of ingredients for a recipe for a given date
in the past.
tIngredients holds current ingredients and their current cost per
ounce.
tHistoryOfIngredientCosts is a separate, unlinked table, holding at
least one record showing the name of the ingredient (IngredientName),
the starting cost of the ingredient (CostPerOz) as well as the date
the ingredient was first used (Start_dt).
If an ingredient changes cost, a new record is appended to
tHistoryOfIngredientCosts, showing the IngredientName, the new
CostPerOz and the Start_dt at which the new cost was applied.
tHistoryOfIngredientCosts is sorted, first by IngredientName and then
by Start_dt.
User wants to enter a date to determine the costs for each recipe on
that date.
(I am not sure how database should capture this date)
I think I should be able to use tHistoryOfIngredientsCost to determine
the cost of ingredients on that date.
I hope to create a report which uses the date supplied by the user to
determine the cost per ounce for each ingredient on that date.
From cost per ounce on the date in the past, I would be able to
calculate cost of recipes on that date in the past.
Thank you in advance for any light you can shed on this problem.
