X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Please Help With My Meal Plan Database., Access 2007    
post Jul 8 2015, 06:20 AM

Posts: 3
Joined: 8-July 15

Hi All,
Hope you guys and gals can help.
I'm relativity new to access and am developing a recipe/meal planner database just for myself, family and friends (and of course if any of you want it then that's cool too cool.gif )
There is a recipe table (RecipeT) and form (RecipeF) in which to enter in the recipe name, instructions etc. Linked to this through RecipeID as a foreign field is a table and form for ingredients (IngredientT and IngredientF).
There is a menu plan table (MenuPlanT) and form (MenuPlanF) in which a user will select a specific recipe for Monday breakfast, Monday lunch, Monday dinner, Tuesday breakfast etc etc. When you have selected the recipe, then the RecipeID is stored in the corresponding field in MenuPlanT.
So the idea is that from this meal plan I will be able to print out a list of groceries for the week, using the linked ingredients table, as well as be able to print out a meal plan so that there is non of this "what should we have for supper" nonsense that usually ends in us just buying takeaway sad.gif

I'm relatively competent when it comes to creating forms and reports so I should be OK there but where I am having issues is that link between the recipe selected in the menuplanT and combining the ingredients to get a total quantity for me to print out. So what I was thinking was adding a button on the MenuPlanF which writes the ingredients to a new table called say ShoppingListT which is linked using the MenuPlanID. But how exactly i would go about doing this i have no idea. Please help.

I've uploaded the database as I have it now, please excuse how raw it still looks (its a work in progress). I do plan on adding a main menu and all those other nice things grinhalo.gif

Thanks in advance.
Attached File(s)
Attached File  Recipe___Database_2.zip ( 316.95K )Number of downloads: 40
Go to the top of the page
post Jul 8 2015, 07:56 AM

Posts: 903
Joined: 25-April 14

I would have a form, to select the menu plan to print.
The query would use this selection on the form as its criteria to pull the ID.

Join this table to all items in the ingredient table. Now you just print the report using this query OR
build an append query to write it to a shopping list table.
Go to the top of the page
post Jul 8 2015, 08:13 AM

Posts: 2,428
Joined: 12-February 15
From: SW AZ

I've only taken a quick look at your database. From what I've seen, I believe the biggest problem you're having in developing your shopping list is your database has not been properly normalized.

This is particularly true with your MenuPlan, in which you have a field for each of your daily meals (Monday Breakfast, Tuesday Lunch, ...). This makes it extremely difficult to tabulate the ingredients for your shopping list. Your table is still workable, but it'd require around 50 (or more) times than that of a normalized table, as well a making documenting the process enormously more difficult.

Two other problems I've identified in you MenuPlan: (1) Meals usually consist of more than one dish. (2) How many servings do you need to make?

Considering this, I believe your menu plan should be broken into two tables:
MealID, Primary Key, Autonumber
MealDate, Date/Time (gives date and day of week)
MealTypeID, Foreign Key, Number (reference to a new table, MealTypes: Breakfast, Lunch, ...)

tblMealItems (dishes)
MealItemID, Primary Key, Autonumber
MealID, Foreign Key, number (reference to tblMeals)
RecipeID, Foreign Key, number (reference to your recipes table)
MealItemServings, number (servings you need to prepare)

Other changes in tables and approach will also need to be made (e.g. you don't need to the RecipeID in the Ingredients, the same ingredients are used in all dishes; add a ingredient weight field, convert volumes of say flour to a weight, ...)

Before you go (much) farther, you should try reviewing the Newcomer's Reading List. I believe you'll save a lot of time and have less wasted effort if you do so. We, of course, are always willing to help with specific assistance.
Go to the top of the page
post Jul 8 2015, 08:23 AM

UA Admin
Posts: 35,898
Joined: 20-June 02
From: Newcastle, WA

You need to invest some time in learning how relational databases work before trying to move into forms design.

We have an excellent set of articles to help you get your feet under you.

The MenuPlan table is designed more like you would have to do it in Excel. This is a fairly common mistake made by first time Access users. Here's a very good blog that explains this problem and how to correct it.

Also, it looks like you have the relationship between recipes and ingredients set up inappropriately. Each recipe contains one or more ingredients and each ingredient can be used in one or more recipes. That calls for a junction table, which allows you to handle this relationship.

So, spend some time studying the principles you'll need to follow in redesigning your tables, then take a shot at doing so. Post back those results for further review and guidance. In the meantime, we don't need to spend any time on forms or reports yet.

Best of luck with your project.
Go to the top of the page
post Jul 8 2015, 08:31 AM

Posts: 3
Joined: 8-July 15

Thanks so much for your replies.
Glad to know its just a fundamental flaw in my database rotflmao.gif
I will definitely have a look at how to properly normalize the database as well as go through all the resources you guys have posted.

At least I have some direction.
Hopefully in a week or so ill be able to post back.
Thanks again for the awesome help.
Go to the top of the page
post Jul 8 2015, 08:35 AM

UA Admin
Posts: 35,898
Joined: 20-June 02
From: Newcastle, WA

As a rule of thumb, we try not to think too much about form design too early in the process. Because all data must be handled appropriately in tables, that comes first.

Good luck with the project.
Go to the top of the page
post Jul 8 2015, 10:12 AM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin


The table structure setup is the single most important step in database design; it's like the foundation to a house. The better the foundation, the less problems you have later on.

Here's a basic structure I'd start with, based on your description:

MealtimeID [Primary Key, Autonumber]
strMealtimeName (Breakfast, Lunch, Dinner, Tea Time, Second Breakfast, Elevensies, Midnight Snack, Brunch, etc.)

UnitOfMeasureID [Primary Key, Autonumber]
strUnitOfMeasureName (Pinch, cup, tablespoon, gallon, pound, kilogram, stalk, etc.)

IngredientID [Primary Key, Autonumber]
strIngredientName (Carrots, hamburger, etc.)

RecipeID [Primary Key, Autonumber]
strRecipeName (Rosanna's Tiramisu, Mom's famous BLT sandwich, etc.)

RecipeIngredientID [Primary Key, Autonumber]
lngRecipeID [Foreign Key to tblRecipes.RecipeID]
lngIngredientID [Foreign Key to tblIngredients.IngredientID]
curQuantity (I use the Currency datatype because it handles up to 4 decimal places of accuracy much better than Single or Double)
lngUnitOfMeasureID [Foreign Key to tblUnitsOfMeasure.UnitOfMeasureID]

MealID [Primary Key, Autonumber]
lngMealtimeID [Foreign Key to tblMealtimes.MealtimeID]
lngRecipeID [Foreign Key to tblRecipes.RecipeID]

With this basic structure, you can have as many (or as few) meals per day as you like. (Some people (like my mother-in-law) have fasting days for dietary or religious reasons, others (like Dwayne "The Rock" Johnson) have extra meals because their lifestyle dictates it. Also, you can easily create a list of ingredients with quantities for a shopping list by querying all of the meals between two dates ("What ingredients will I need to purchase for the food I am planning for next week?") Plus, you can have multiple recipes for a single meal. (Broiled pork chops and homemade applesauce for dinner requires separate recipes for the chops and the applesauce, for example.) Also, since your recipe ingredients include not only the ingredient, but the quantity and unit of measure, you could probably add calorie/fat/whatever information as well, if you need to.

Hope this helps,

Go to the top of the page
post Sep 11 2019, 08:44 PM

Posts: 2
Joined: 23-October 18

Great answer and marvelous help, Doctor9. I see how easy was to build a Weekly Meal planner that inmedially start building one base on your recomendation. But once I build the tables I do not know how to make the relationship and which fields should I build the Lookups. Can you go a little more dipper with some instructions. I will real appreciate it.
Go to the top of the page
post Sep 12 2019, 12:57 PM

Posts: 15
Joined: 2-October 18

Here are some data model relationship examples including for recipes, ingredients and grocery planning.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    22nd October 2019 - 02:49 PM