UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Recipe To Grocery List, Access 2016    
 
   
MOMO88
post Dec 31 2017, 08:44 PM
Post#1



Posts: 93
Joined: 2-August 17



Would it be difficult to make a database that you enter all the ingredients and quantity needed for the recipes that you use, and when you select the ones you want for meals that week it automatically populated a grocery list report with the quantity you need? for example. you choose tacos, then it automatically adds the 1lb of hamburger, tomato, taco seasoning, etc. If you want Spaghetti, it adds the 1 lb hamburger meat to the already 1lb to show you need 2lbs and so on. this is just a fun database i thought about and want to practice the little i know and hopefully learn a lot. Thanks for any help.
Go to the top of the page
 
GroverParkGeorge
post Dec 31 2017, 10:12 PM
Post#2


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Yes, it is quite possible. The devil, as they say, is in the details.

You'll need tables for recipes and ingredients. You'll also need a junction table in which to record the ID for each recipe and the IDs for each of the ingredients.

When you have created records for each recipe and each of the ingredients you will shop for to go into those recipes, it is then relatively easy to create queries to select the list you need to print.

Of course, that's a high level description and the details depend on specifics of how you need to structure your recipes and the ingedients.

WIth more specifics, we can probably help you get closer.

But with it being New Year's Eve, all of this might need to wait a while?

--------------------
Go to the top of the page
 
MOMO88
post Jan 1 2018, 09:52 AM
Post#3



Posts: 93
Joined: 2-August 17



Thank you for the reply. I mostly just wanted to put this up last night while i was thinking about it. I don't expect this would be done in a single day, nor do i think it will be high priority for anyone. It is something i wanted to make sure from people that know more about access than i do that this would be possible before i tried to work on it and ask a bunch of questions for something that will not work. I look forward to seeing how this turns out.
Go to the top of the page
 
GroverParkGeorge
post Jan 1 2018, 10:13 AM
Post#4


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


I strongly urge you NOT to sit back and wait for someone to do it for you. It would go much faster if you take some positive steps, such as searching for other posts on recipes ( I know there have been several over the years) as well as trying your hand at designing and building some tables that you can upload for review and feedback. That way you can participate in the process from the beginning. You'll get plenty of input, of course, as people begin to see your ideas about what you really want and need.

--------------------
Go to the top of the page
 
MOMO88
post Jan 1 2018, 10:22 AM
Post#5



Posts: 93
Joined: 2-August 17



i have no intentions of having anyone do it but me. I stated that above. I wanted to make sure before I started that it would be possible. Thank you for the help
Go to the top of the page
 
MadPiet
post Jan 1 2018, 10:28 AM
Post#6



Posts: 2,412
Joined: 27-February 09



a Recipe is a pretty simple concept. If you're only looking at ingredients, and not quantities, it's super simple. The hard part is when you have vastly different units of measurement... teaspoons vs pounds etc. Otherwise it's just

Recipe---(1,M)--Contains--(M,1)--Ingredient
Go to the top of the page
 
MOMO88
post Jan 1 2018, 10:42 AM
Post#7



Posts: 93
Joined: 2-August 17



I seen this earlier this morning and it is almost exactly what i have in my head. https://youtu.be/GOJJsiE9cGo. It even shows the shopping list. The only thing i would really like different is to have the shopping lists combine into one so that instead of having 4 list that all need 3 pieces of chicken breast, it would all be combined in a single list that shows you need 12 pieces total. I also want to make the choosing the meals as easy as checking a box and then being able to print the menu of the selected meals and the shopping list to make them all. I know this will be hard and i have a lot to learn. That is why i chose it. Just to make it clear. I want to make this on my own, but I know i will need help from people that know more than me. I do not expect to put a idea up and someone say here ya go. If something like this is already built and someone is willing to give me a copy that is great, however, i will still build by own also for the knowledge from doing so. thanks everyone.
Go to the top of the page
 
GroverParkGeorge
post Jan 1 2018, 11:05 AM
Post#8


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Go for it. Put something together, based on what you've seen and read. Upload it here for review and feedback.


--------------------
Go to the top of the page
 
MOMO88
post Jan 1 2018, 12:24 PM
Post#9



Posts: 93
Joined: 2-August 17



The attached is where i am to so far. Any suggestions on how to get this to a point where i can have a list of all of the recipes and be able to have check boxes to select them. The ones selected will then make a report that is a meal menu and then another report that is a shopping list that combines the like ingredients and show a total quantity needed based on the measurements given. thanks for the help.
Attached File(s)
Attached File  shopping_list_database.zip ( 181bytes )Number of downloads: 4
 
Go to the top of the page
 
GroverParkGeorge
post Jan 1 2018, 12:31 PM
Post#10


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


It occurs to me that there was a quite recent, similar request for a recipe database. Is this the same one by any chance?

--------------------
Go to the top of the page
 
GroverParkGeorge
post Jan 1 2018, 12:33 PM
Post#11


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, that ZIP file contains only the locking file for the accdb. Can you try again, making sure the accdb gets selected?

Thanks.

--------------------
Go to the top of the page
 
MOMO88
post Jan 1 2018, 12:33 PM
Post#12



Posts: 93
Joined: 2-August 17



This is the first time I have asked. The one attached I have worked on only this morning and everything in the database I have built myself through the use of youtube videos and trial and error.
Go to the top of the page
 
MOMO88
post Jan 1 2018, 12:36 PM
Post#13



Posts: 93
Joined: 2-August 17



sorry about that. try this
This post has been edited by MOMO88: Jan 1 2018, 12:36 PM
Attached File(s)
Attached File  shopping_list_database.zip ( 887.21K )Number of downloads: 9
 
Go to the top of the page
 
MadPiet
post Jan 1 2018, 01:22 PM
Post#14



Posts: 2,412
Joined: 27-February 09



You only need a few tables for this:
Recipes (RecipeID, RecipeName...)
Ingredients (IngredientID, IngredientName...)
and then the junction table...
RecipeIngredient (RecipeID, IngredientID, Quantity, Units)

Then your shopping list is something like

SELECT IngredientName, SUM(Quantity)
FROM Ingredients i INNER JOIN RecipeIngredient ri ON i.IngredientID = ri.IngredientID
WHERE... <select which Recipes you want>
GROUP BY IngredientName;

your Shopping List would probably just be a report with no details, just summary info (you could do just a regular select query as the source for the report, and then do the totals in the report instead of in a query.)
Go to the top of the page
 
GroverParkGeorge
post Jan 1 2018, 01:28 PM
Post#15


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Hey, not a bad start at all. I see a few things we can clean up and one significant change to the tables, but you're really headed in the right direction.

What you have now in the "combine" table is a design we call "Repeating Groups". It's a common misstep most of us make, or have made. It comes from "thinking like a spreadsheet". I.e. columns instead of rows. Or more accurately, Fields instead of records.

While we're working on some suggestions, please go study these blogs by Roger Carlson, and look at his sample db..

This is relatively easy to fix. It will take just a little while to do it, though.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Jan 1 2018, 02:02 PM
Post#16


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Here are some suggested revisions to bring the basic design closer to a normalized structure and more conventional naming convention. WHile names are not crucial to success, a good naming convention goes a long way towards minimizing ambiguity.

You'll see that I made these changes

a) added a UnitofMeasure table to correct the problem of repeating fields.

b) removed those lookup fields in tables. Keep them only in forms, where they work fine.

c) used a more traditional naming convention--you can further modify it if you have another one you like, but please avoid spaces in all names.

d) Enforced Relational Integrity on all relationships. RI is fundamental to sound table design.

Attached File  shopping_list_database_Normalized.zip ( 827.67K )Number of downloads: 6


With this structure, a "shopping list" query can be created and used to print a shopping list as a report. One additional element may be needed, of course, and that is the "number of servings" that would be required to support a recipe to be made for 3 people as opposed to 30 people, for example.

--------------------
Go to the top of the page
 
MOMO88
post Jan 1 2018, 06:39 PM
Post#17



Posts: 93
Joined: 2-August 17



Thank you all for the help so far.
Go to the top of the page
 
MOMO88
post Jan 1 2018, 07:24 PM
Post#18



Posts: 93
Joined: 2-August 17



I thank I am going to stick with the changes you made. I am not finding a way to have the recipes go into the two reports like i have them in my head. If i could get help to make a selection form that would have two buttons. The selection form would allow to select however recipes needed for the week and then send the records to the menu report and then make the shopping list. how would i start something like that? thanks for the help.
Go to the top of the page
 
MOMO88
post Jan 2 2018, 09:07 PM
Post#19



Posts: 93
Joined: 2-August 17



I am stuck. On the Menu Report that I have, I for some reason can not get the ingredients quantity and unit of measure to show the text. I am also having trouble figuring out the shopping list report. I know some code was given to me above, I am not getting it to do anything. Thanks for the help.
Attached File(s)
Attached File  shopping_list_database2.zip ( 886.6K )Number of downloads: 4
 
Go to the top of the page
 
MadPiet
post Jan 3 2018, 04:54 AM
Post#20



Posts: 2,412
Joined: 27-February 09



Oh where to begin!

First off, your query for the report is a bit off. You don't need to show the join fields in your query at all. It's enough that they're there in the FROM clause and join the 3 tables together. (Think of it as link to get from one record to its related records.)

So your query would be something like this:
SELECT tblRecipe.Recipe, tblRecipe.Instructions, tblRecipe.Picture, tblRecipeIngredient.IngredientsID, tblRecipeIngredient.UnitOfMeasureID, tblRecipeIngredient.Quantity, tblRecipe.RecipeID, tblRecipe.[Select Recipe]
FROM tblIngredient INNER JOIN (tblRecipe INNER JOIN tblRecipeIngredient ON tblRecipe.[RecipeID] = tblRecipeIngredient.[RecipeID]) ON tblIngredient.IngredientID = tblRecipeIngredient.IngredientsID
WHERE (((tblRecipe.[Select Recipe])=Yes));

Strictly speaking, you don't need RecipeID, IngredientsID, UnitOfMeasureID in your field list at all. you need the RecipeName, IngredientName, and UnitOfMeasure. (Sounds like you've been using that awful Lookup misfeature, where you see one thing (the value an ID is associated with) while storing another (the ID value). So when you get to your report, it throws you completely.

Grouping... The RecipeIngredient stuff goes in the Detail section. (it repeats once for each recipe item). The rest goes in the Recipe header. (RecipeName etc) - because you only want to show that once.

The RecipeIngredient stuff in the Detail section...
Ingredient.Name
RecipeIngredient.UnitOfMeasure
RecipeIngredient.Quantity
Ingredient.IngredientName

the joins are fine - you just have to pull data from the actual field containing the TEXT, not the ID stuff. (Think of the ID stuff as keys for joins and indentification... beyond that, you don't care about it - it's not for "show" in your reports/forms.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 12:40 PM