Making Combos with Menu Items and Ingredients

[Jace Campbell]

My program allows you to enter ingredients, then build menu items using the
ingredients. It then calculates based on case cost and quantity how much it
costs for the ingredient in your menu item. Then on a form it sums the
calculated fields to give a total food cost for a menu item.

I would like to allow users to combine multiple menu items to create a combo
like a hamburger basket. The problem is that the cost for the menu item is
not stored anywhere, it is simply calculated and summed when it is selected
on the screen or on a report.

Right now the user builds a menu item by selecting ingredients from a combo
box that has my ingredients table as the record source.

I am afraid I cannot do what I want to here because of a flaw in my table
design, but I would appreciate many more second opinions and suggestions as
to how to tackle this problem. Below I have provided my table structure for
further guidance.

Ingredients Middle Menu Items
ing_name-----------------ing_used menu_price
ing_price menu_used------------menu_item
ing_qty qty-used

For each record in the Middle table, the quantity is multiplied by the
ingredient cost per item in a query and then summed.

Thank you so much in advance for taking your time to look at this. Please
let me know if there is anything more I can provide.

 

[George Nicholson]

Having faced a similar situation once upon a time, I inserted another level
and made a distinction between ProductItems and MenuItems.

ProductItems are composed of Ingredients, just how you have MenuItems now.
Things that are produced by the kitchen (i.e., available products). For
example, this would include various sizes of Fries.

MenuItems can be a single ProductItem or multiple ProductItems combined into
a basket. It requires another recipe table just like you have between
ingredients and Products, but at a higher level. MenuItems represent how the
available products are presented/marketed to the customer (i.e., what the
customer actually sees on the Menu). If you have 3 Fry sizes, you might have
3 possible Hamburger meal combinations: one for each Fry size. (Either that
or some sort of "upsize/downsize" adjustment).

Optionally, a MenuItem might also have a "quantity" of ProductItems other
than 1 (i.e., a 2 hamburger for $2.00 "deal")

If you are dealing with Sales prices, they would be stored only at the
MenuItem level (promotional pricing like 2 for $2.00 being the best example
of why).

Please don't ask me about drinks. Sizes are one thing. Sizes and Types
(Coke, DietCoke, etc.) is something else. Our solution was based on how the
client's data was already set up - which I won't tell you - and it was as
good a solution as any. Not particularly elegant, but usable and as good as
any alternative I could think of.

HTH,

 

[Jace Campbell]

Thank you so much George. I will begin working on adding another level
immediately.

 

[Jace Campbell]

I did what you said and created a new product items table and middle items
table to join the Product Items table and Menu Items tables together. I have
my first query and form made where I can enter ingredients to build the
Product Items. It correctly calculates on the form and all is well.

On the Menu Items form and query, I am having trouble getting the form to
sum all the ingredients from the Products Item to create a total for the Menu
Item. In the query for the menu item form I used the chain of tables going
down each level but then I used the Product Items query so I would have the
calculated amount for each item. When I do this, the combo boxes on my form
become locked and I cannot enter new products to the menu.

I have tried calculating all of the ingredients on the menu item form all
from the base level ingredients. I have made sure all of the properties are
set to allow editing. I have tried to Sum the calculated fields in the menu
items query. I have tried making a stored number in the Product Names table
to keep up with the cost calculated in the other query. I am stuck. I've
been working on this all day and have read dozens and dozens of posts. I
would appreciate any ideas anyone has. I am sure I am missing something very
obvious.

Thank you for your help.