Fitness 12
By Richard Rost
9 months ago
Creating Meal and Meal Detail Tables, Calculated Query
In this Microsoft Access tutorial, I will show you how to set up meal planning in a fitness tracking database by creating tables for meals and meal details, entering sample data, and building a query to calculate total calories and protein for each meal. This is part 12.
Members
There is no extended cut, but here is the file download:
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!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, fitness database series, meal planning, meal table, meal detail table, food items, bundled meals, group meals, meal groups, food groups, calories calculation, protein calculation, table relationships, query design, subform, combo box, nutritional data, primary key, foreign key, data types
Subscribe to Fitness 12
Get notifications when this page is updated
Intro In this video, we continue with part 12 of the fitness database series in Microsoft Access by focusing on meal planning. I'll show you how to create tables to store meal descriptions and meal components, set up foreign keys, and choose the right data types for quantities. We will enter sample meals, link meal details to food items, and add new foods directly when needed. I'll also guide you through building a query that calculates total calories and protein for each meal by joining the meal detail and food tables. This is part 12.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. This is part 12 of my fitness database series.
Whether or not you care about fitness, this database series has lots of tips and tricks for all kinds of databases. Today, we're finally going to get into meal planning. So, we've got food items in our database. Now we're going to put those together into meals so you don't have to keep saying "all right, I want to add cereal with soy milk with coffee." You can just put in "breakfast" and it will populate your standard breakfast. Then, it will be bundled as a meal. The same thing will work whether you're dealing with products that have components or any kind of grouped items.
If you haven't watched parts one through eleven, go watch those and then come on back.
We are finally to meals. I was thinking about making meal groups because we have food groups, where you could group different types of meals together, like all your dinners can be grouped under dinners and all your lunches under lunches. However, me personally, I have maybe half a dozen or maybe ten meals that I actually keep track of as a bundle. If it gets to the point where we need to add it, we'll add it later. That will be good because I'll show you how to retroactively add something like that. But I think for now, just a single list of meals would be plenty.
If you want to put it as "breakfast, cereal" or "breakfast, eggs," that's fine. You can name these whatever you want to, but for right now we can get away without doing meal groups.
If you're familiar with my other databases, the meal system will be analogous to orders for the customer database. For example, we'll have a meal table that has the information about the meal itself - basically its name and notes - and then a meal detail table, which is analogous to the order detail table that has all the items on it and the quantities. Then we'll do the math there, like calculating the total number of calories and stuff.
Let's create a couple tables. First, create Table Design.
We're going to build our meal table. This will be the meal ID (our auto number). We need a description (short text), notes (long text), and that's pretty much it. Save it as meal t, and we're done with that one.
Let's make another one: Create Table Design. Let me show you real quick before I do this. Here is my spreadsheet that I've been using myself personally. Here are all my food items - we've already got all this in there. These are different meals that I've set up: my fish rice, fish rice and vegetables. One or two cans of fish (I think I do two cans), a couple rice cans and mixed vegetables, a little bit of olive oil, and that's it. That's a meal.
Here's another meal: turkey sandwich (turkey, cheese, bread, lots of mustard). Here's Rick's coffee: coffee has no calories, but I use collagen creamer and a little bit of Coffee Mate. Gotta have Coffee Mate. Love the sweetness. It's only 30 calories to make my coffee bearable. I don't like black coffee, and I try to get away from using cream.
Anyway, these are all examples of meals. See, I only have five of them here.
Now we have the meal table, so we're going to make the meal detail table. Meal detail ID (that's our auto number), meal ID (that's the foreign key, so that's a number of type long integer), food ID (also a foreign key to the food table that indicates what items are on there), and then a quantity (how many of that thing). Make it a number and default it to one. If you just add something, you'll have one of them.
That's it. You could put a description or notes or any of that stuff here if you want to, but I'm not going to bother.
If you want to learn more, the invoicing system that I build goes over pretty much the same kind of thing we're building here today.
That covers relationships and all that stuff too.
Now, people always ask me, "What if the data changes when you're building an order?" For example, you're putting together an order with products on it. Let's say you're selling a computer: you put the hard drive on there, the memory on there, the monitor on there. If those product item prices change in the future, you don't want it to change your order, so you have to copy that data to the order details, right?
Well, that's not going to happen with food. An apple is always going to have the same number of calories and carbs and sugar. This data is never going to change, and if you change the food item, that just means you learned something new, and that's what it should be. If it's a different kind of apple, make it a different item. But this stuff is never going to change, so by linking it directly to the food table, that's fine here. You don't have to store copies of that information in here.
Olive oil is always going to have the same nutrients in it unless you get a different brand, or a different type, or a different kind. In that case, make a new item for it.
So, we don't need to store historical data in this table.
Let's save this as our meal detail t. Primary key, yes.
Let's put some sample records in.
For this, I would like to see my food table because we'll need the IDs here. I'm going to sort this by description in the table just so we can see stuff.
Let's open up the meal table, put that down over here, and we're going to have forms for all this. I just like to put sample starter data in my tables because it's easier to build the tables if you can see some data in here.
Let's create our first meal, and we'll call this "Fish Rice Veg." That's meal one.
So, meal one is going to start off with some fish. Let's find our fish. Fish, one can, right there. That's food ID 38. Quantity: let's do two cans of that.
Next, we need to find some rice. Rice, one cup, is food ID 44. One cup of rice.
Then, meal one, we need to put our vegetables in here. Mixed vegetables, one can, that is 42.
Finally, we've got the olive oil. There's my olive oil, that's 43, and one drizzle. It's never a teaspoon, it's always more than a teaspoon. I estimate myself.
So, that's meal one.
For meal two, let's do morning cereal.
This will be meal two. Let's find my Catalytic Crunch. Cereal, Catalytic Crunch, right there - that's food ID 1. We'll do one cup of that.
We might want to actually make quantity a double, because I sometimes have a cup and a half. You'll catch little things like this when you actually start working with it. So let's change this field.
For quantity, instead of a number of type long integer, let's make that a double. I only ever use long integers and doubles. There are other types, but don't worry about them. I have a whole video as to why. Go watch this video, it talks about all the different data types and why I only use those two types.
Basically, you have integers for counting numbers - just use a long integer. For anything with a decimal, a floating point, just use a double. It will hold everything you need to hold.
If you really want to optimize, you can shrink it down to just an integer or just a byte. That was a big deal 30 years ago when the cost of storage space was really high. Now, don't bother; just use a long integer or a double.
This will allow us to say I have a cup and a half of blueberries, for example, in my meal.
So, save that, close it, and open it back up again.
We are not done with our meal here. So, we've got two, we've got one and one.
For meal two, we need soy milk. Where is soy milk? Do I have it in here? I don't think I have it in here. We'll quickly add it. I'm going to put it under dairy, that's one I believe. Soy milk; I don't know what the nutrients are on it right now; we'll look it up later. Now, at least I have an ID and can stick that in here - 56 and one. That's good enough for now.
Close all this up, save changes, yes. All right, good to go.
Before we build the forms, we're going to build a query that does the math for us. In here, I know what the food item is and how many of them I have. This can calculate the total number of calories or protein or whatever you care about. We'll do that in a query.
Create Query Design.
I'm going to bring in the meal detail table. The nutritional data is in the food table, so we'll bring in the food table as well. You won't be able to have an item in your meal detail table without it being in the food table, because we're only going to pick from a combo box later. So we don't need to worry about the join type here. We're not going to let people just type stuff in.
I'm going to bring in meal t, meal detail t. That will bring us all that information over.
We need calories and protein. You can bring over whatever you care about – if you're tracking cholesterol or sugar or whatever. I'm just doing these two things to keep it simple.
Then, we're going to calculate our total calories, which is calories times quantity (how many you have on there). I'll do the same thing for total protein. So, total protein is protein times quantity. You can do that for everything you care about. I just care about those two.
Save this as my meal detail q.
When I run this, you can see there are my calculations.
This is what's going to go in the subform. We'll have a combo box to pick the food ID, type in the quantity, and then display the calories and the protein based on the amount. We'll do that in the next class, part 13.
Today is Thursday, July 24, 2025. No, it's not really, but I'm recording this for release on that date. So tomorrow, as far as you're concerned, is Quick Queries Friday. We will be tackling part 13 on Monday, the 28th. So tune in then, same bat time, same bat channel.
Members, you can watch it right now because I'm on a roll. I'm going to record a bunch of them tonight.
That's going to do it for your TechHelp video for today. That's part 12. Hope you learned something. Live long and prosper, my friends. I will see you Monday for part 13.
TOPICS:
Creating a meal table for meal descriptions
Creating a meal detail table for meal components
Defining table fields including foreign keys
Setting quantity field to use double data type
Entering sample meal records with food items
Linking meal detail to food items by ID
Adding new food items directly in the table
Building a query to calculate meal calories
Calculating total protein and calories per meal
Joining meal detail and food tables in a query
COMMERCIAL:
In today's video, we're continuing with part 12 of the fitness database series by learning about meal planning. We'll set up tables for meals and meal details, allowing you to bundle your favorite foods together so you can add a whole breakfast or lunch to your daily log with one click. You'll learn how to relate meals and their components, set up quantities using the proper data types, and build a query to calculate totals like calories and protein for each meal. Next time, we'll work on creating forms to bring your meal entries to life. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the primary purpose of introducing 'meals' in the fitness database described in the video?
A. To group individual food items into a reusable bundle for convenient data entry
B. To calculate the price of combined food items for restaurant billing
C. To track the expiration dates of food items
D. To restrict users from entering new food items
Q2. In the database analogy presented, how is the meal table related to the order table in a customer orders database?
A. Meal table is identical to the order table
B. Meal table holds overall meal information much like the order table holds overall order information
C. Meal table stores all food items, unlike the order table
D. Meal table is only used for storing nutrition facts
Q3. What type of field is recommended for 'quantity' in the meal detail table to allow for fractional servings?
A. Short text
B. Currency
C. Double
D. Boolean
Q4. Why does the instructor choose not to store copies of food nutrient information within the meal detail table?
A. Because food nutrient information is unlikely to change and can be reliably referenced from the food table
B. To save disk space and reduce table size
C. Because nutrients are irrelevant for meal planning
D. To prevent duplication of food names
Q5. Which of the following is NOT a reason given for using long integer or double as number types in the database?
A. They are sufficient for most data needs in modern applications
B. Other types are required for historical data storage
C. Disk space optimization is not as important as it once was
D. Simplicity and reliability in design
Q6. When calculating the total calories or protein for a meal, what data is multiplied together in the query?
A. Calories or protein by price
B. Calories or protein by food group
C. Calories or protein by quantity of each food item
D. Calories or protein by meal ID
Q7. Why is it unnecessary to store historical food data in this meal planning database?
A. Food item attributes like calories typically do not change, and if they do it reflects a new distinct item
B. The database is intended for one-time use only
C. The instructor dislikes historical tracking
D. Historical data is automatically backed up elsewhere
Q8. Which structure is set up to list the foods included in a meal and their amounts?
A. Meal table
B. Food table
C. Meal detail table
D. Data types table
Q9. What would be an appropriate way, according to the video, to handle a different brand or variant of a food item with distinct nutrition facts?
A. Overwrite the existing food item
B. Add a new food item to the food table
C. Remove the item from all meals
D. Change the meal name
Q10. According to the instructor, what will be the benefit of building a query with both the meal detail and food tables before creating forms?
A. It allows calculation of totals like calories and protein for each ingredient based on quantity
B. It ensures all data is encrypted
C. It restricts meal entry to administrators only
D. It creates automatic backup copies of the records
Q11. How does the instructor suggest populating tables during the initial creation stages?
A. By importing data from external web sources
B. By adding sample starter data directly to see and understand the data
C. By writing custom SQL scripts to insert hundreds of fake rows
D. By skipping data entry until all forms and queries are built
Q12. What is the main reason the instructor says meal groups (such as "all dinners") are not needed at this stage of the database?
A. Meals are already grouped by calorie count
B. The instructor only tracks a small set of actual meal bundles and can add grouping later if needed
C. Grouping is impossible in Access databases
D. Grouping makes it harder to enter new meals
Answers: 1-A; 2-B; 3-C; 4-A; 5-B; 6-C; 7-A; 8-C; 9-B; 10-A; 11-B; 12-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.Summary Today's TechHelp tutorial from Access Learning Zone focuses on creating meal planning functionality for your Microsoft Access fitness database. Even if you're not interested in tracking fitness specifically, the techniques shown here are widely useful for managing grouped items of any type in a database.
At this point in the series, we have already covered setting up tables for food items. Now, we're ready to leverage those food items by combining them into meals. This means you can conveniently select a meal, like "breakfast," and the system will automatically populate it with all its standard components instead of having to enter each item individually every time. This setup mirrors how you might manage any group of assembled products or packages in a different context.
If you've missed earlier parts of this series, I recommend watching parts one through eleven to get up to speed.
Today, we're introducing the concept of meals. While it's possible to create an additional layer to organize meals into categories such as "breakfasts," "lunches," and "dinners," for most people a simple list of meals suffices. Personally, I only track a handful of recurring meal combinations. If there comes a need to further categorize meals in the future, we can add that later and I will show you how to do it retroactively at that time. For now, let's keep the structure straightforward.
In the context of other databases I've built, the meal system is much like the orders component in a customer database. We'll have a meal table to hold essential details like the meal's name and any notes. Accompanying this will be a meal detail table, which lists the specific food items and their quantities for each meal, essentially mirroring the way an order detail table would work.
Begin by creating your meal table. This table will include an auto number for the meal ID, a short text description, and a long text field for notes. After setting this up, save it as your meal table.
Next comes the meal detail table. Before you set it up, let me provide a little context based on my own tracking spreadsheet. I use the spreadsheet to list out my food items, and then group those into different meals: for example, "Fish Rice and Vegetables" with a breakdown of each component like two cans of fish, a couple cans of rice, mixed vegetables, and a drizzle of olive oil. I have similar groupings for things like turkey sandwiches and my own coffee routine. In practice, I only maintain a handful of these meal bundles.
Now, for the meal detail table: create an auto number for the meal detail ID, then add a meal ID (which will link back to your meal table), a food ID (linking to the food table), and a quantity field. Set the default quantity to one, but remember this can be adjusted as needed. Optional fields like description or notes can be added here, though they are not strictly necessary.
If you're interested in a deeper dive into this type of structure, the invoicing system video tutorial covers similar ground, including the use of relationships between tables.
People sometimes ask how to manage changes in the underlying data, such as a product price change affecting historical order records. With food items, the nutritional data is unlikely to change unless you are dealing with a distinctly different item. For instance, the nutritional content of an apple will not change, but switching from one variety to another should be treated as a different item. Therefore, you can link directly from your meal detail table to your food item records without worrying about preserving historical snapshots.
After creating your meal detail table and saving it, you can start entering sample data. It's helpful to have the food table open so you can reference the appropriate IDs. For example, to create a "Fish Rice Veg" meal, you can assign fish as the first item, then rice, mixed vegetables, and olive oil, each linked using their respective food IDs and the right quantities.
For another example, "Morning Cereal" might include a cup of cereal and a cup of soy milk. This leads to a useful realization: sometimes you'll want to specify fractional quantities, like a cup and a half of something. To support this, change the quantity field in the meal detail table from a long integer to a double. I usually restrict my numeric fields to long integers for whole numbers and doubles for anything with decimals. Optimizing further by using smaller data types is largely unnecessary with today's storage capabilities.
Once your tables are set up and you have some sample data, the next step is to build a query that will calculate nutritional summaries, such as total calories and protein content for each meal. Start by creating a query that brings in both your meal detail and food tables. Link them appropriately, as every meal detail record should be associated with a valid food item. Add fields such as calories and protein, and create calculated fields by multiplying those by the quantity, resulting in total calories and total protein for each line item. You can extend this for any other nutritional information you care to track.
Save this as your meal detail query and run it to see the calculations in action.
This query will become the foundation of a subform later on, where you'll be able to select a food item, enter a quantity, and view calculated nutritional information for each meal entry. That is what we'll focus on in the next lesson.
For those keeping track, today is Thursday, July 24, 2025. The next lesson in this series will be available on Monday, July 28th, continuing the step-by-step process.
That concludes our lesson for part twelve of the fitness database series. For a complete video tutorial, with all the step-by-step instructions on everything discussed here, visit my website at the link below.
Live long and prosper, my friends.Topic List Creating a meal table for meal descriptions
Creating a meal detail table for meal components
Defining table fields including foreign keys
Setting quantity field to use double data type
Entering sample meal records with food items
Linking meal detail to food items by ID
Adding new food items directly in the table
Building a query to calculate meal calories
Calculating total protein and calories per meal
Joining meal detail and food tables in a query |
