|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Chef's Kitchen Helper By Richard Rost 
6 years ago
Determine Possible Meals Based on Available Ingredients. This video will show you how to use a query to determine what dishes you can make based on the available ingredients. Not limited to just the kitchen. You can use this to calculate needed components for any product that involves multiple parts. Whether you're selling food, computers, or machine shop parts, these techniques work for everyone. Camila from Milan, Italy (a Platinum Member) asks: I run a small kitchen. I would like to have a database that includes a list of all of the recipes for the dishes that I make including the ingredients, the amounts required, and the amount I have on hand. I would like to be able to quickly see what dishes I can prepare based on the ingredients I currently have in my pantry. Is this possible? MembersI'll show you how to look at the actual quantity of the products you have on the shelf and calculate if you have enough to make a dish. Instead of just "yes, I have eggs" you can say "I have 12 eggs" and calculate accordingly. Additionally, in Access Developer 19, I expand on this topic even more and show you how to do it with recordsets, and how to click a button to make a meal and remove the product components from inventory.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! Links
IntroIn this video, you will learn how to set up a simple Microsoft Access database to help manage kitchen recipes and pantry inventory. I will show you how to create three tables for recipes, ingredients, and the products you have on hand, and how to link them together to determine what dishes you can prepare based on your current pantry stock. We'll use supporting queries to check which recipes are possible with your available ingredients. This video provides a step-by-step walkthrough to build a basic recipe and inventory management system in Microsoft Access.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. My name is Richard Rost, your instructor.In today's video, which I'm calling the Chef's Kitchen Helper, I'm going to show you how to set up three tables: one for your recipes, another one for the ingredients that each recipe has in it, and a third table, which is your product inventory - the stuff you actually have in your pantry on your shelves. So I can say, these are the recipes that I have, and these are the ingredients that I've got. What can I make for dinner? What can I make for my kitchen? I can make cereal, I can make pancakes, I can make chicken parm, based on what's in my pantry. That's what we're going to cover in this lesson. Today's question comes from Camilla from Milan, Italy, one of my platinum members. Camilla says, I run a small kitchen. I would like to have a database that includes a list of all of the recipes for the dishes that I make, including the ingredients, the amounts required, and the amount I have on hand. I would like to be able to quickly see what dishes I can prepare based on the ingredients I currently have in my pantry. Is this possible? Yes, of course, Camilla, there's always a million different ways to do things in Access. Let me show you one method that's relatively easy using a couple of queries. Here I have a blank database. Let's create a table. We're going to need three different tables. One to hold our products. So, table design: we need a product ID - that's my auto number, a product name. Don't just make it "name." Remember, "name" is a reserved word - you want to try to avoid using that. Short text. And then we're going to put in here "on hand." Do I have this particular ingredient on hand or not? We're going to start off with just a yes or no. Later on in the extended cut, I'll show you how to do it with actual quantities. For now, we're just going to keep it simple, because this is actually kind of complicated as it is. Save this as my productT, primary key, of course. Let's put some values in here. Go to datasheet view. Let's put some products in here: how about chicken breast? And I've got some. Eggs, yeah. Flour, yeah. Let's see, spinach. Sure. Pancake batter. OK. Milk, and yeah, OK. How about some breakfast cereal? I don't have that on hand. And how about parmesan cheese? Yeah, I got that. Some marinara sauce. OK, and one more thing. How about pasta? OK, pasta? You pick - bowtie, linguini, whatever your favorites are. Here's our products. Now, I'm going to shrink this up a little bit because I'm going to put a couple different things on the screen here at the same time. Save this. Shrink it up a little bit. Leave it over here on the side. There's one product table. Let's create another table. This one will be for our recipes. Now, this will be the recipe parent form. The recipes are just going to have the recipe ID and the recipe name, and that's it. Save this, recipeT. Primary key. Yep. Open this back up in Design View. Just some simple recipes. Just the recipe name. It's the parent. We're not going to put our ingredients in here. Remember, this is a one-to-many relationship between recipes and the ingredients in the recipe. If you're not familiar with how to do one-to-many relationships, watch my video on relationships first, then come back to this one. If you don't know how to relate multiple tables together, this is going to be impossible for you. That's a free video. It's on my website. It's also on YouTube. I'll put a link in the description down below the video. Recipe names: cereal. You see where I'm going with this? Pancakes. And chicken parm. This is just the parent, so picture this as being like a parent form. I'm not going to go through the craziness of building forms for this example. We're just going to work with the data and the tables. Now, last table. Create table design. This table is going to hold the ingredients for each recipe. So, ingredientID - auto number. Now, it belongs to a recipe, so it gets a recipeID, that's a foreign key. And it's going to also have a productID. Save that, ingredientT. Now, let's put some data in this table. Here, basically, I'm going to slide it like that so you can tell this is underneath this one. So recipe 1, cereal, has two products in it. It's got breakfast cereal, which is product ID 7, and milk, 6. Just looking at the table, you can see there's recipe 1. Ignore this ID over here - don't even need it. Recipe 1, cereal, has products 6 and 7 in it. That's how this works. One-to-many relationship with the third table over here for this value. Next, pancakes. What do we need for pancakes? For pancakes, we need 5 and 6 - pancake batter and milk. So, 5 and 6. See how this works? Now for chicken parm, it's a little more complicated. Chicken parm needs chicken breast. Chicken parm also needs parmesan cheese. Chicken parm also needs marinara sauce and some pasta to go with it. Technically, I guess you don't, but the way we serve it in our restaurant, we do. You can see here now that just by looking at the table, I should be able to make everything on here except breakfast cereal, because I have no breakfast cereal. So the cereal recipe should come back and fail, but the other ones should say OK. At this point, this is just a simple example. We're just checking to see whether I have it or not. I'm not checking quantities. I might need two gallons of milk and I only have a pint. We'll do that in the extended cut. Let me slide this. It's easier if we can build our queries while looking at this stuff. Sometimes it helps to see this. Sometimes I actually even print out my tables and keep them on my desk while I'm building a database. There's my three tables. Again, you'd build a form. This would be your parent form. This would be the subform inside it. You'd replace this with a drop-down box. If anyone really wants to see me flesh this out into a real nice database, the forms and stuff like that, let me know, and I could put it together as a separate lesson. This video was requested by one of my platinum members who also has taken some of my developer classes, so I know she knows how to build this stuff. We're going to need a couple of different queries first as supporting queries before we build the main query that's going to show us what we can make or not. First thing we're going to do is generate a list of all of the recipes and their ingredients that I actually have in stock. In other words, show all of the recipes that have ingredients that don't have this little guy here marked as no. Create, create design. We're going to bring in all three tables: the recipe table, the ingredient table, and the product table. Let me close this. Now, you should see the little relationships pop up there. That's fine, that's normal. We can leave the relationships just as they are. Outer joins are fine. I'm going to bring in the recipeID, the recipe name, and let's bring in quantity on hand. Run this query. You can right-click on here and go to Datasheet View if you want to. I have the run button put up here on my Quick Launch Toolbar. Now you can see this is what I got so far. Here's each recipe and whether the ingredients are in stock or not. Looking at this, I can get an idea for which ones have all their items. This one has four items that I need. This one has two. This one also has two. Now, in Access, a yes-no value has a value of 0 if it's false and negative 1 if it's true. In some other database systems it's positive 1; in Access, it's negative 1. We can use that. We'll know how many total ingredients we need, and this will tell us how many we have. We're missing an ingredient from the cereal. So if I can sum these up somehow and then multiply by negative 1, obviously, then I can see if this has all of the ingredients that I need. Let's make this into an aggregate query. How do we do that? Design tool - design tab of the ribbon. You'll see this little guy here - it's just total. It's called an aggregate query now. Click on that. If you've never done an aggregate query, I've got videos on that. I'll put a link down below. Now it's got "group by," which means it's going to group all the similar values together. If I run the query now, it looks like this. Notice how there are separate ones here for cereal. There's a yes and there's a no. These guys are all grouped together because they're all the same. That's what a "group by" does. Now, I don't want to group these individually. I want to add these up - sum them up. Run it, and now I get a count for the items that I have on hand. I've got two items on hand for pancakes, four items on hand for chicken parm. They're negative because it's negative one, remember? We're going to fix that in a minute. Cereal, I've only got one out of the two ingredients. Let's save this guy. This will be my recipe ingredient in stockQ - that's what ingredients I've got in stock. Now, summing it up will tell you how many you have. How many you need is simply a matter of the total count of the ingredients. Right next to it here, let's put in "on hand" again. This time let's count the number of items that there are. Save it again. Run it. Look at that. This is how many are needed. This is how many I have. You can see I only have one out of two. Let's fix this and make it so it's not negative. In design view, let's call this one "QTY on hand:" and it's going to be "on hand times negative one" like that. On hand. And over here, let's call this guy "quantity needed:" like that. Now run it. There you go. There's your quantity on hand and your quantity needed. Unfortunately, this is as complex as this query can get. You can't do any calculations on here or check to see which one's greater than the other one. It won't work. You have to make another query. This query has to generate first. You get these results, which is basically what we need. Now you can make another query to say, is the quantity on hand greater than or equal to the quantity needed? So we need to make a second query. This happens a lot. This becomes a subquery inside the other one. Create query design. Let's pull in this query. Let's bring in the fields we need. Let's bring them all in. I'm not going to use a star though because I'll put some criteria down here. The quantity on hand has to be greater than or equal to quantity needed. Be careful if you do that. Notice I got quotes. I don't want quotes; I want brackets because this is a field and not the actual word. Save that. This will be the canMakeQ. This will show you what products you can make, what recipes you can make. There you go. The quantity on hand has to be greater than or equal to the quantity needed. See that? We don't need to show this guy here. We can probably just delete it right out. We don't need it in the results field. We can probably hide that field. We don't need to show it in the results to get what we need. Now you can pull this data into a list box or another form or whatever you want to do to show what recipes you can make based on what you have out here. Let's save this. Let's change things up a bit. So right now, we can make pancakes and chicken parm. Let's say I go to the store, I buy some breakfast cereal, but I'm out of marinara sauce now. Make sure you leave that record, by the way, because notice the little pencil there. That means this record's dirty - it hasn't been saved yet. You have to leave the record for it to save that record information to the table. Now, if I run canMakeQ, look at that. I can make cereal and pancakes, but I'm out of marinara sauce, so I can't make that. If I close this and come back in here and do that, go to the store again, run it, all three. If I'm out of milk, now I should only be able to make chicken parm. See how that works? The tricky part is this query right here. In design view, this guy. You have to bring all these together. Each recipe has these ingredients. Each ingredient is this product. Group these together. Then you have to bring in your quantity on hand, which is the sum of how many this should be. The count over here gives you the count of the total number of records. The sum is a trick we're using because yes values are negative one and no values are zero. So we can add them up to know how many we actually have in our inventory. This is the easy way to do it. In the extended cut, I'm going to show you how to do it with actual quantities. Instead of saying yes, I have chicken breasts, yes, I have eggs, I can say I have four chicken breasts and 12 eggs. We'll see how that works in the extended cut. In the extended cut, I did just what I said I was going to do a moment ago. I set it up so you can have a quantity on hand amount and a unit type. You've got either "each" like chicken breasts or eggs, flour in pounds, spinach in ounces, milk in cups and so on. You can put that in your inventory for your pantry. Then your ingredients, you'll specify the same type of quantity, and then the query will show you what you can make based on what you have enough quantity for. Then I also went a little further with it and I added a couple of lessons to my Microsoft Access Developer 19 course that I'm actually in the middle of right now. You can find this on my website; I'll put a link down below. I used a couple of recordsets, nested recordsets with some list boxes and stuff, the same basic stuff that we did with the queries. However, with recordsets, you can do it with programmatic loops. It's a whole lot neater, it's a whole lot cleaner. It works a lot better, I think. We're covering recordsets right now in Developer 19, so I figured this was an excellent example to put in there. Same basic concept. You fill in your tables with the quantity amount that you have and the quantity that you need, and then it will fill a list box here with what you can put together as a dish. I went one step further than that and in lesson seven, I made it so that you could pick one of these guys, click a "make one" and then it will remove the quantities of each of those from your inventory. Each time you make something, it pulls those parts out from your inventory. This doesn't work just with your kitchen pantry. I've had a lot of customers before that said, I've got my product list, but each product is made up of separate components. I used to build computers back in the 90s. I used to sell computers. If a customer said I needed an alpha computer system, whatever the model number was, I know it takes this motherboard, this case, this hard drive, this processor, and so on. I could pick the one computer system and then my system would remove those components from my shelf. So whether you're doing food in the kitchen or whether you're doing manufacturing with different components, these techniques work for whatever you're doing. If you just want to see the simple lesson, then it's in the members only extended cut. You can join my channel. Silver members and up get access to all of my extended cut videos. How do you become a member? Click on the join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the join button, you'll see a list of all the different membership levels that are available, each with its own special perks. But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free. QuizQ1. What is the main purpose of the Chef's Kitchen Helper database described in the video?A. To manage shopping lists for a kitchen B. To track expenses in a restaurant C. To determine which recipes can be prepared based on current pantry inventory D. To order products from suppliers Q2. How many tables are created in the simple version of this kitchen helper database? A. One B. Two C. Three D. Four Q3. What information is stored in the 'productT' table? A. Recipe instructions and products used B. Products available and whether they are in stock or not C. Recipe names and ingredient lists D. Only quantities of products used Q4. Why should you avoid using the field name "name" in Microsoft Access? A. It is too long B. It is a reserved word in Access C. Access does not allow field names with vowels D. It cannot be searched easily Q5. What is the relationship between the recipes and the ingredients tables? A. Many-to-many B. One-to-one C. One-to-many D. No relationship Q6. What is the function of the third table, 'ingredientT'? A. To store price information for each product B. To list which products are needed for each recipe C. To manage inventory restocking D. To organize recipes into categories Q7. In Microsoft Access, what value does a 'Yes/No' field have when marked 'Yes'? A. 1 B. 0 C. -1 D. 2 Q8. Why is an aggregate query (using "group by" and "sum") used in this solution? A. To keep recipes organized alphabetically B. To count how many ingredients in a recipe are in stock C. To sort the ingredients needed by expiration date D. To print the inventory list Q9. What purpose does the second query 'canMakeQ' serve? A. To list all available products in the pantry B. To show only the recipes where the ingredients required are all in stock C. To display all recipes regardless of inventory D. To update the prices of products Q10. If an ingredient is not available in the pantry (marked as 'No' in 'On Hand'), what happens to the recipe in the queries? A. The recipe will always show as available B. The recipe will not appear in the 'canMakeQ' result C. The recipe is automatically deleted D. Nothing changes Q11. When using Yes/No fields in Access, why is the sum multiplied by -1 in the query? A. Because Access does not allow negative numbers B. To correct the count since 'Yes' is stored as -1 C. To sort the results in descending order D. To filter out incomplete recipes Q12. What is mentioned as a more advanced feature planned for the extended cut of the video? A. Adding images to the recipes B. Tracking actual quantities and unit types for inventory C. Printing formatted shopping lists D. Sending inventory updates by email Q13. The solution described can be adapted for which of the following other than kitchen recipes? A. Managing travel itineraries B. Manufacturing with products made up of components C. Scheduling employee shifts D. Graphic design project management Q14. What method does the extended cut use to update inventory after a recipe is prepared? A. It leaves the inventory unchanged B. It removes the used quantities from inventory C. It asks the user to manually update the inventory D. It resets the whole inventory Q15. What is an example of a practical application outside the kitchen, as described by the instructor? A. Scheduling client appointments B. Tracking computer system builds and their required components C. Organizing a music collection D. Managing social media posts Answers: 1-C; 2-C; 3-B; 4-B; 5-C; 6-B; 7-C; 8-B; 9-B; 10-B; 11-B; 12-B; 13-B; 14-B; 15-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on building a Chef's Kitchen Helper database with Microsoft Access. I'm going to explain how to create three essential tables: one for recipes, another for the ingredients needed for each recipe, and a third for your product inventory. With this setup, you'll be able to compare your recipes to the items you have on hand and easily determine what dishes you can prepare from your pantry supplies. For example, you might want to know whether you have the ingredients to make cereal, pancakes, or chicken parm based on your kitchen inventory.The idea for this video came from a student who runs a small kitchen and wants a database listing all her recipes, the ingredients required for each, and a way to track amounts on hand. She is looking for a way to quickly see what recipes are possible based on her current ingredients. This is entirely possible in Access, and I'm going to show you one straightforward way to do it using a few queries. To start, I created a blank database and designed three tables: First, there's a product table to store each item you could have on hand, such as chicken breast, eggs, or milk. Each product gets an ID, a name (remember to avoid reserved words like "name" in Access), and a simple yes/no field indicating whether that item is currently in your pantry. For now, we're using this basic yes/no format to keep things simple, although in the extended cut I explain how to handle quantities. I added some sample items to this products table, marking which ones I had in stock. This forms the backbone of your product inventory. Next, I created a recipes table. Each record has a recipe ID and a recipe name. This table simply stores each recipe as a parent record, not the ingredients yet. If you're unfamiliar with setting up one-to-many relationships in Access, it's important to review that topic because you'll need it for this setup. After that, I built a table for recipe ingredients. Each entry in this table links a recipe to one of its required products, setting up the many-to-many relationship between recipes and products. For example, the cereal recipe uses products like cereal and milk, mapped by their product IDs. Once the tables are filled in, you can see which recipes require which products at a glance. With the basic example set up, it's already apparent that if you don't have a certain product, some recipes will be unavailable. To actually determine which recipes you can prepare, Access queries are the next step. The first supporting query cross-references your recipes, their ingredients, and your product inventory to display recipes and indicate whether you have each required ingredient on hand. In Access, yes/no fields are stored as 0 for no and negative one for yes, so you can use this to your advantage in calculations. I used an aggregate (totals) query to sum up the "on hand" values for each recipe's ingredients. This lets you count the number of ingredients in stock for every recipe. By multiplying the sum by negative one, you can straighten out the math so it reports positive counts, matching each to the total number of ingredients needed per recipe. With this summary in place, I created a second query to actually compare the quantities: it checks whether the quantity on hand for each recipe is at least as much as the number of ingredients required. This tells you exactly what you can make with your current supplies. Once you've built these queries, you can use them to populate list boxes or reports to present the results to your users. For example, you might want to list only those recipes that are currently available, given your pantry's inventory. I also walked through an example of updating your inventory and seeing how the available recipes change in response. If you buy more cereal or run out of marinara sauce, the results in your query will update accordingly, showing you what now is or isn't possible to make. The key challenge in this solution is the query logic, which ties together your recipes, ingredients, and stock levels. By leveraging aggregate queries and Access's intrinsic handling of yes/no values, you can efficiently determine which dishes you can prepare. This video covers the basic version using simple yes/no flags for stock tracking. In the extended cut, I go beyond this to demonstrate using actual inventory quantities, such as specific numbers of eggs or pounds of flour. The queries are adjusted so you can specify both the quantity on hand and the unit (each, pounds, ounces, cups, etc.) for every ingredient and product. Then, the system will show you what you can prepare based on having enough of each item. Beyond that, in my Developer 19 course, I expanded the solution further using recordsets, allowing for more flexible and programmatic checks, and even the ability to decrement inventory automatically as you use ingredients to prepare a dish. This can be adapted for manufacturing or assembly scenarios too, not just kitchen management. If you want to follow along with all of the steps and see the detailed process, a complete video tutorial with step-by-step instructions is available on my website at the link below. Live long and prosper, my friends. Topic ListCreating a product inventory table for pantry itemsCreating a recipes table for storing dish names Setting up a table for recipe ingredients Establishing one-to-many relationships between tables Populating product, recipe, and ingredient tables with sample data Understanding yes/no data types in Access Building a query to list recipes and ingredient stock status Using aggregate queries to group and sum ingredient availability Calculating number of ingredients on hand per recipe Counting total ingredients needed for each recipe Adjusting aggregate queries for positive quantity values Comparing ingredients on hand vs. needed using queries Creating a query to display recipes you can make with current inventory Testing dynamic recipe availability by modifying inventory data |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access components, inventory, pantry, chef, kitchen, products, parts, pieces, multiple, restaurant, stock control, retail PermaLink Chef's Kitchen Helper in Microsoft Access |
