Problem getting the data I need!

[mseeker22]

Hello,

I'm having trouble getting the data I need from a query, here is the background:
I am working on a database for costing meals to go on restaurant menus.

I have a the following Tables:

- T_Dishes
- T_Food_Products

The main form is based on T_Dishes which allows the user to complete various fields including Dish_Name & upto 15 ingredients in fields 'Cbo_Ingredient_1', 'Cbo_Ingredient_2' etc (These are combo boxes that retrieve Product_Name and Wholesale_Price_Per_Unit from T_Food Products) and then the user completes the quantity used of each ingredient in fields 'Txt_Qty_1', 'Txt_Qty_2' etc

Using the 'Wholesale_Price_Per_Unit' data, another field 'Txt_Dish_Cost' then calculates the total of each ingredient and adds them together

e.g. sum([Cbo_Ingredient_1].Column(2)*[Txt_Qty_1])+([Cbo_Ingredient_2].Column(2)*[Txt_Qty_2]) etc



Now this is all fine but I want to be able to use this result elsewhere in the database (Other forms & reports) but I do not want to store a calculated value.

My hope was to create a query that could perform the same calculation that the form does and then base other forms on said query.

My ideal query for this scenario would have the following output fields:

Dish_Name & Dish_Cost, nothing else is required at this stage.

The problem I seem to have is that unlike in the form, I can't reference '.column(2)' in my query criteria . . . and despite many hours working on a solution I've gone round in circles and ended up with nothing . . .

Hope this makes sense

Many thanks in advance!!

[jzwp11]

Your problem lies here:

upto 15 ingredients in fields 'Cbo_Ingredient_1', 'Cbo_Ingredient_2' etc

What you are describing are repeating groups which is a violation of normalization rules. This site has an overview of normalization rules. What you are doing is trying to use Access as if it were a spreadsheet program which it is not.

If a food dish can have many ingredients (food products), that describes a one-to-many relationship which by normalization rules requires a separate but related table. Addtionally, if an ingredient can be used in multiple food dishes that describes another one-to-many relationship. When you have two one-to-many relationships between the same two entities (dishes and ingredients) that is a many-to-many relationship which requires the additional table to be a junction table.

So a table to hold the food dish info (1 record for each dish)

tblDishes
-pkDishID primary key, autonumber
-txtDishName

...a table to hold all possible ingredients. Each ingredient would be a record in this table

tblIngredients
-pkIngredientID primary key autonumber
-txtIngredientName

...now the junction table

tblDishIngredients
-pkDishIngrID primary key, autonumber
-fkDishID foreign key relating back to tblDishes
-fkIngredientID foreign key relating back to tblIngredients
-spQty (quantity of ingredient used in the given dish)
-fkUoMID foreign key to tblUnitsOfMeasure


tblUnitsOfMeasure
-pkUoMID primary key, autonumber
-txtUoM

Now as to the cost of ingredients, are you planning on where the ingredients come from (vendors) and can an ingredient be supplied by multiple vendors at different prices? I assume that your unit price for an ingredient has to be based on some unit of measure and that you might use different (smaller) units of measure for your actual dish recipes.

[mseeker22]

Thank you for taking the time to explain that for me.

I do already have seperate tables for Suppliers, Units of Measure, Food Categories & Menus.

I think the key problem I missed was that its a many to many relationship between Dishes & Ingredients (Food Products).

I'll have a go creating the third table changing the relationships. One question, do I now base the main form on the new 'joint' table?

[jzwp11]

You would base the main form on your food dish table and the subform on the junction table. In the subform, you would use a combo box based on the ingredients (foodproducts) table.

[mseeker22]

I don't understand how I assign all 15 ingredients on the subform . . . do I need a set of:

-fkIngredientID foreign key relating back to tblIngredients
-spQty (quantity of ingredient used in the given dish)

for each ingredient?

I think I've missed something :-/

[jzwp11]

I don't understand how I assign all 15 ingredients on the subform . . . do I need a set of:

-fkIngredientID foreign key relating back to tblIngredients
-spQty (quantity of ingredient used in the given dish)

for each ingredient?

Each ingredient pertinent to the dish would be a record for which you would supply the ingredient and its quantity--so 15 records in the subform. I typically use the datasheet view for the subform.

I've attached an example database. Check out the form called frmRecipes (opens when you open the database). The chocolate cake recipe has 3 ingredients while the pizza dough recipe has 2.