Food Menu Database....Please Help

[ann smith]

I've posted this post earlier and I got a few responses from people who had
no idea of what I was really trying to convey. Therefore, I will try again
and I'm hoping that my writing skills will make things a lot clearer this
time.

I'm trying to create a food menu database complete with recipes and
directions/procedures on making the item. This is what I want.

I want the end user to be able to use the drop down menu feature in forms.
From that point, the end user can choose a Menu Food Item (let's say
hamburger). A new page will appear and on that page will have the title
Hamburger. Also, the ingredients for making the hamburger. Also, the
directions/procedures on making the hamburger. The end user will be able to
print this page out and refer to it when they want.

Is there a way to do this? I don't even know how to setup the tables for
this. Should there be two separate tables? One for Menu Item and the other
for recipe? I am somewhat new at Access (2000) and I'm hoping someone can
give me a push in the right direction.

Any help is appreciated. Thank you.

 

[Jim/Chris]

I would set up atleast 3 tables
Food item
Ingredients
Directions/procedures
These tables would be joined by a common key with a one to
one relationship

Then create a form with a combo box based on the food item
table. You could include all the fields in that table on
this form. You can even throw in a picture.

Add two tabs containing subforms
First tab would contain a subform base on the ingredients table
The second tab would contain a subform based on the
directions/procedures table
All the subforms would be linked by the above mentioned
common key.

One thing to remember forms are for editing and viewing
data, nor for printing. So you would create a seperate
report(s) for printing of the food item and
directions/procedures and ingredients.

There is more than one way to skin a cat. This is one option.

I hope this is a start

Good Luck

Jim

 

[Kelvin]

Before you begin you need to keep one thing straight. What you see on the
screen and what gets printed are 2 separate things. What you see would be a
form. Therefore any type of user interface would be done using a form. To
print something out to the printer you need to set up a report. Its true
you can print out the form, but it will not look clean due to background
colors on the form. You can also view a report on the screen but it will be
hard to read since it is basically a print preview of what will be printed.
So for what you want to do, you may have to create both a form so the persom
can see it on the screen and a report so that the printed version will look
good as well.

Since recipes usually have 3 parts, general info, recipe list, and
instructions, I would create 3 tables. The first table for the general
info will contain RecipeID, FoodItem, FoodDescription, Comments. The second
table for the recipe will have RecipeID, ItemID, RecipeItem and the third
table for the instruction will have RecipeID, InstrID, Instruction. ItemID
and InstrID are so that you can keep each step separate incase you need to
revise or add a step.

In contrast to the above you could put everything in 1 big table with
RecipeID, FoodItem, Recipe, Instruction. Where Recipe and Instruction are
memo fields to contain everything in 1 large paragraph.

For the form you mentioned, this would be easy. Just create an unbound form
with a combo box linked to FoodItem. The AfterUpdate even of the combo box
will call up either the form or the report depending on which method you
choose. I would suggest the form, then from there have another button to
print to the printer. By using both, you can use the form to add and edit
the recipes. By having another buttoin to print the recipe, you could
actually have multiple buttons to print the current recipe, all recipes, all
recipes of a certain category (you will need to add a category field to the
table), only the recipe list, only the instructions, and many other
variations.

Kelvin

 

[Jim Shaw]

I've been thinking about building the same type of
database myself. Here are a few of my design points:

Some recipies are actually assemblies of other recipies.
For Example, a turky recipe would have child recipies for
the bird itself, the brine solution, the stuffing, gravy,
etc. That would lead to a bi-directional, reflexive
relationship in the food items table. One direction for
the assembly components, the other direction for "Where
Used" navigation. The reflexive relationships would have a
lead time offset to allow make ahead specifications.

There is an associative relationship between the food
items and ingredient table. The relationship table would
have the quantity needed for each recipie.

What I have not figured out is how to treat recipie by-
products. For example, using the leftover turkey carcas
to make turky soup.

Other things to think about in the design would be, meal
planning, nutritional data, special dietary requirements
such as peanut allergies, diabetic diets, etc.,
alternative ingrediants. For example, if a recipie calls
for fuji apples and you can't get them, what do you use?

Hope this helps.
-Jim

 

[talcummalcolm]

One way this can be done is to set up a QBF (Query by Form). Using th
before mentioned idea, set up the three tables containing th
infomation required.

Create a new form that is not based on any table or query, and save i
as QBF_Form.
On the new form, create one combo box and one command button that ha
the following properties.

Name combo Box One : Menu Selection (Or something like that)i
properties change theRow Source Type to either Value List then Type i
the Menu Type names ie. Hamburger;cheeseburger into the value list, or
which is better create a table holding only the names Ham/Cheeseburge
and select Table/Query in Row Source and select the table name in valu
list.
On the Command Button in Properties, change caption to Search, an
OnClick, type QBF_Macro. Then, save the form:

Create the following new macro, and then save it as QBF_Macro: Selec
OpenQuery for Query Name type QBF_Query, select Datasheet in the vei
catergory and Edit in Data Mode.

Create the following new query that is based on the Orders table, an
then save it as QBF_Query:
Select the field which has your criteria, in criteria typ
Forms![QBF_Form]![Menu Selection]
Drag in to the query all other required fields

Set start up so that the QBF form runs on start up, or, create
command button on an excisting form to open the QBF Form.
One the form is open you should be able select the required menu item
click the command button to run the query, this will display th
required ingrediants from the three tables. From this you could creat
a report from the qry.

Hope this helps