Fitness 2
By Richard Rost
7 months ago
Food Table Design, Nutrition Macros, Food Form
In this Microsoft Access tutorial, I will show you how to continue building a fitness tracking database by setting up tables for food items with macros, creating forms for quick data entry, and adding useful features like a clickable URL button using VBA. We will also update the main menu to focus on the new fitness database and organize the interface for tracking food and workouts efficiently. This is part 2.
Members
In the extended cut, we will learn how to put a button on your food form to connect to OpenAI using the API and get nutrition macros directly into your database without copying and pasting. I will show you how to set up the button and automate fetching the data using the API.
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, food table, macros, meal plan, daily worksheet, calorie tracking, protein tracking, fiber tracking, sugar tracking, fat tracking, food form, VBA, FollowHyperlink, API, OpenAI, nutrition data, ChatGPT, auto number, StatusBox, form design, meal grouping, exercise plan
Subscribe to Fitness 2
Get notifications when this page is updated
Intro In this video, we continue building the Microsoft Access fitness database by setting up tables for tracking food items, meals, and meal plans, as well as creating a daily worksheet to monitor your intake. I will show you how to build a food table with nutrition information, enter sample foods, design a form for easy data entry, and use VBA to add a button that opens web links directly from your records. You'll also see how to pull nutrition data from ChatGPT, customize your forms, and update the main menu. This is part 2.Transcript Welcome to one of the TechHelp videos brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today is part two of my fitness database series, so if you did not watch part one, go watch that so you know what this is all about. Let's go.
Now, in a lot of my databases and a lot of my series, you hear me say that it is best to sit down and start to plan all your tables out and all that stuff. One of the benefits of having been doing this for several months now in Excel is that I have a lot of it already prototyped out in Excel. I have exactly what I need, like I am in a food table, right, to track all my food items and their macros, calories, protein, carbs, whatever. So that is one table. I am going to need another table to track my meals. Meals are going to be groups of food items.
For example, what you see there is one meal. Let me put this in the foreground here so you can see this better. One meal here is going to be one can of fish, one cup of rice, and one bag of frozen mixed vegetables. Put those together, and that becomes a meal, which you can give a name to, like "fish and rice," "fish, rice, and veggies," or whatever you want to call it. It will be 620 calories, and then we can put the meals into a meal plan.
Here is one of my meal plans. I have different meal plans for different days depending on what I am doing that day. Here is my cardio day plan: breakfast, a snack, lunch, two cardio sessions. My butt cannot handle 30 straight minutes on the bike, even with a padded seat, so I break it up into 15-minute breaks before and after dinner. As I get lighter and my backside gets more in shape, I will be able to do longer on the bike. Here you go. Here is dinner, for example, and that is a meal. We will build a little table to track that. Then you take the meals, and you put them in your meal plan.
It does not matter if you are dealing with food or computer parts. It is all the same stuff, all the same techniques. What I like to do is, and we are also going to build this, I like to have a daily worksheet. You can either set it up in advance or add things in here as you are doing them. Breakfast is pretty much standard. Snack, I need that midday snack, then I got lunch. I have not gotten to this point yet, so in the database, we will have maybe little checkboxes over here: have you done this yet? My bedtime snacks are always pretty much the same thing. Now, I have a big question mark here for dinner, and I know I have got about 500 calories to work with. That is not that much, but it is enough.
That will be our worksheet, our daily worksheet. Then, when we are done for the day, you hit a button on the bottom, and it logs it so you can track all your stuff in the past. When you are wondering why you have not lost any weight, you can look at your log and see that you have been in a calorie surplus for the past two months. You have to cut out that extra... I do not know, whatever. Sometimes what I do is actually set it so that if I have gone over by, like, 200 calories, I calculate that means I need to spend an extra 20 more minutes on the bike at the end of the day. There are all kinds of tricks you can play, but we are going to focus more on the fundamentals.
Another thing we are going to work out is the food part first. Then, we are going to work on the workout stuff. We will have different exercises, and you could track your max weight that you are using. Like I said, I currently track my workouts on a whiteboard, and that is one of the reasons I want to build a database to track this in, because it is very chaotic. You can see here that here is my original setup, and then I have what I did the last time. We will do the same thing when we get into the workout part of it.
You get set up with exercises, then you get set up with an exercise plan, which would be kind of like meals - group them together. You might have a leg day. You might have a push-pull day, or whatever splits you are doing. For losing weight, I find the best thing is to do three full body workouts three times a week, alternating with cardio days. When I am in a building mode - if I want to gain muscle and add weight - then I go into a push day, a pull day, a leg day, and then a core day. Whatever your goals are, this will work. Set up your exercise plan, and then again, you will copy that into your daily worksheet, which you then can track.
What do you have to do today? What is your max weight, the reps that you are doing, and all that stuff? Eventually, I would like to put this on my phone or on a tablet or on a flat screen in my garage so I do not have to use this big whiteboard for everything. As you can see, it is nuts. It works, but it is crazy.
Now, like I said before, I am going to try to call out prerequisite stuff as we are going. I have not built anything yet, so we are going to be building it together. I am going to start off by using my TechHelp free template as the initial template for this database because it has a lot of the stuff in it that I already want to use. It has my continuous and single forms and a main menu built. Although, we are going to gut a lot of the stuff out of here.
If you are new to my channel and you want to learn how I built that database, start here with the blank template, as there are a couple different videos that go through. Follow along with the videos and they will explain everything up to that point.
We really do not need any of the tables in this database. I am going to delete those because we are not going to be working with contacts, customers, or any of that stuff. Delete. We do not need any of these queries, goodbye. I am going to keep these two because those are my blanks, basically. We do not need this one, this one, or this one. Keep the main menu, keep the Not Trusted, get rid of the order stuff here, get rid of these reports, and keep all that stuff down there. When you are done, you should have left this stuff.
In case you do not know, this Not Trusted form means that when the database opens, it runs the open main or the autoexec. The autoexec checks to make sure that your database is in a trusted folder. If not, it pops this thing up. If you have not seen this yet, this is because I would get a million people that would follow my videos and download it and say it would not open on their system. Now I program it so that when you download this template, it will open this up and say, "Oh, you are not running the database in a trusted location." That saves me a lot of emails.
As I said before, I think the best place to start is our food items table. Right now, the only things I really care about tracking are calories and protein, but I am going to add some of the other macros as well, not all of them, just some of them. I know some of you care about carbs and some of you care about fat. Obviously, this is a teaching database, so you can customize it however you want. If you want to track cholesterol, that is up to you. Track that if you have high cholesterol, great. I am not going to put everything in the database, but a lot of it.
Let us take this guy for example. I cannot believe I discovered this. This is amazing. This is my favorite cereal of all time. It is wonderful, high in protein, and low in sugar. Here is what it has got: 10 grams of protein, 130 calories. That is pretty good. 14 grams carbohydrates. I care about sugar and try not to eat stuff with added sugar.
We will track fat. I do not buy stuff with trans fats, so I am not going to bother tracking that. Cholesterol, I do not care about. Sodium, I do not care about. You can add whatever field you want. I am going to add the ones that I care about. I am not trying to sell this cereal, but you have got to try it if you like this kind of thing. I will put a link down below. Trust me. Yes, I did use my Amazon affiliate link, so buy some cereal and I will get two cents.
Let us create a table now. Create > Table Design. This will be just a FoodT, so FoodID, an AutoNumber, a Description.
Now, I like to put the serving size in the description. I am not going to play games with the math. For example, the serving size is half a cup. Half a cup is not enough cereal for anybody, by the way, so for me it is one cup. So I will put in my description "one cup," and then I will just double all the stuff in here because I would never eat half a cup of cereal. That is just not enough.
Next up, I would track calories. That is going to be a long integer. We are not tracking fractions of a calorie here. I will track fat. I track total fat sometimes. Cholesterol, I do not care about. Sodium, I do not care about. We will track total carbohydrates.
You can spell out carbohydrates if you want to. I am just going to keep it in here as "carb." I do like to make sure sometimes I am getting enough fiber, so I will track fiber. I do like to track total sugar. Number and added sugar. Sometimes, for things like if I am looking for a treat, I will ask, "Which one of these has the least amount of added sugar?"
Then of course there is protein. That is my most important number for me, next to calories.
Most tables are going to have notes of some kind. That will be long text. I am going to actually put a URL in here, which will be where I buy it, because when I am running low on it, I might want to just click on the URL. For example, if I buy it on Amazon, it will just take me right there. As time goes on, we are probably going to come up with some additional stuff, so that is good for now. I am going to save this. This will be my FoodT, food table.
Let us put a couple of items in here.
Let us make this a little bigger. We have Description, so Cereal - Catalina Crunch Blueberry (Blueberry Muffin, technically, but all right). I am going to put the serving size in my description: one cup. I do not want to have to keep all that separate math, but I am going to have to double all the calories on the label. So it is going to be 260 calories, fat was 5 so it is going to be 10, carbohydrates is going to be 28, fiber is going to be 14, total sugars is 2, and added sugars is still 0, protein is 10 so it is going to be 20. Notes, and then the URL. I am going to put that Amazon URL that I gave you a minute ago in the notes.
There is that, and that is our first product. We are going to have it later so that when we are setting up our meal plans, we will have a quantity in there. So if you are going to have a cup and a half or two cups, you could put in 1.5. I just find it is easier to actually put the amount that I really consume in the food table instead of having to do math all the time.
We are going to add a few more items in here. A good source to get your calorie and nutrition data is something like ChatGPT. For example, I ask, "Can you please give me the food macros for a banana: calories, fat, carb, and fiber?" and here it is. If you want to not have to type all that in, prompt it differently.I say can you please format that as, here's my list of fields, in a format that I can easily copy and paste in the Microsoft Access tab, separated. Hit the copy button here, that's going to copy it to your clipboard. Then come up here. Now, on a new record, here's the trick: you have to come and get this highlighted, the little cluster. You have to select these fields, exactly the same number of fields that you copied to your clipboard, and then hit paste. Look at that, and I pasted them all in there.
I'm just going to put in here, I don't need all that, I'm just going to say Banana, one medium, that's fine. All right, and there's all your stuff, and that's one way you can get it from ChatGPT. Yes, this is food item three, because I goofed with my first paste, but the autonumber doesn't matter. Remember, autonumbers are not for you; you should not care about them.
Then I'll say, can you give me the same data for a tangerine, same format? Let's see what we get. Of course, here's the data, blah blah blah. Now, I get the small tangerines, I get the little guys, the little halos, are they called? That looks a little better. All right, copy. Come back up here, get the plus like so, and paste. There we go, tangerine, small.
Let's make a single form for this guy. Real easy, I'll take my single form, this guy here, copy and paste. This will be my foodF. Open her up, right click, design. I like green for foods. Let's make this thing green. Let's go like... okay.
Form Design, Add Existing Fields. Oh, we didn't bind it yet. Whenever you see this 'Show All Tables' right there, that's how you know you didn't bind your form. Beginners will click on this, and then come in here and start doing this stuff. But you haven't bound your form yet, so don't do that. Come in here and bind your form first. Record source is going to be FoodT, the only table in the database right now.
All right, caption: Food. Now we can add existing... oh, there we go. Click, click, and then we'll click and drag, drop them all over here. Now, whether or not you want to include the ID on here, that's up to you. You don't really need to. Nine times out of ten, the only reason I include the ID is because I'm trying to teach beginners, so they can recognize what that is and what it's for, and I like to have them see it to become used to it. But you don't really need it on most forms, so I think we're going to delete it. Let's get rid of it. Get rid of that. I keep this guy around just for some formatting, but we don't really need that, too.
All right, let's slide everybody up. Now, description is going to go up here. Let's make that black so we can actually see it. Standard colors, of course. All right, we'll bring you over like this. Description can be pretty big. Underneath, we've got our calories. Let's put this here. It's up to you if you want to angle it in a little bit, or you can put the macros underneath it a little bit like that. I'm just going to take a second and rearrange this stuff, nothing fancy. I don't have to make you sit here watching me move fields around.
All right, there we go. Move everything around a little bit. I'm going to squeeze these in a little bit more. There we go, perfect. I got calories, protein, fat, carb. The carb things underneath carb, kind of bent it in a little bit here. Set notes to my yellow, that's my favorite thing to do, with a little shadow around the back.
All of this I covered, by the way, in my TechHelp Free Template Beginner video, so if you don't know how to do anything I just did, go watch that video series.
All right, let's save it, FoodF, close it down, open it back up again, looks good. I think normally I don't like having numbers left or right justify, but I think this time since that's how all the labels kind of are, maybe I'll do that instead.
What do you think? I don't know. I'll probably change it again five more times before we're done, but anyway, it looks like it's working, and there we go. Go through the next one, the next one.
All right, I like it. Now, here comes a tiny little bit of VBA. You want to go to this. I don't like using hyperlink fields in my database. There is an actual hyperlink data type you can use. I strongly dislike it. This guy here. Where is it? Don't use it. I got a whole video on why you should not use it. This video, go watch this, I'll explain why.
What we are going to do instead is make our own little way to follow this hyperlink. Now, this is where a teeny tiny splash of VBA comes in. So if you don't know VBA and you want to learn, go watch this, it'll teach you everything you need to know to get started in about 20 minutes, and then come back and follow along. If you don't care about the VBA portion, you can stop watching right now, because I'm just going to show you how to make it so you can click on that link and it will open up the URL for you. If not, you can just copy this and put it in your browser.
I'm going to make a little button though. Let's go to Design View. I'm going to leave a little extra room there for a button. Grab a command button, I'm going to drop it right there. Cancel the wizard.
Instead of Command15, let's first give it a name, so OpenURLButton or whatever you want to call it. Let's put a picture on it. I like the little globe picture. Where is it... is it called globe or world? Let me see if I can find it here. Yep, it's a world, and it was one of the two of them. That's kind of like a universal 'go to the web' symbol button. Resize that.
Now, this guy, when I click on this, I want to go to this URL. Right-click, build event, and open up your code builder. Mine came up really large, let me resize it so it fits. There we go.
Real easy. You ready? FollowHyperlink URL. You're done. That is so easy; you just have to know one command: FollowHyperlink, and that will open up your web browser to whatever is in the URL field. So that's it. VBA, it's nice and simple.
All right, debug, compile. You just have to learn a handful of commands, you can do some really cool stuff. All right, so save it, close it, close it.
Oh, we have to fix our buttons here too. We'll get to that. Food form, ready, click, and it opened up in my browser. And yes, I bought it a couple times as you can see. I've actually bought it more than that, I bought it locally in a store a couple times too, I found it actually cheaper on Amazon.
Okay, so that's the FollowHyperlink button. Let's update our main menu. Let's call this the Fitness Database, but don't change the label here in the form because if you do, I've talked about this in a couple of the videos too. Watch what happens: it resizes that label. I hate that. Access team, fix that! Don't resize the label when they change it. I'm going to undo.
Open up, go over to the caption right here, and call this the Fitness DB. Let's call it that. Now, all these buttons here are linked to all kinds of other code, we can get rid of this. We're going to keep the StatusBox here. If you haven't watched my StatusBox video, go watch that because we are going to use this. I'll put a link to that down below too. It's just this guy that I use to display messages instead of popping up message boxes all the time.
But we can get rid of that, we can get rid of all of these, we can get rid of all the code behind this form. I have a button right here that I put on my Quick Launch Toolbar called 'View Code'; that brings you into the code for just that button. But you could find it by just right-clicking on any of these buttons and going to build event. It'll bring you into the same spot.
We don't need any of this stuff, so you can get rid of all of that. Keep the stuff up top though. Keep the Option Explicit, the Option Compare Database. I have separate videos to explain what those do. Google them, or look them up on my site. And of course, leave my copyright notice. Don't touch that. Needs to be updated though, it's 2025.
Let's say this is the Fitness Database. Copyright whatever. Okay, anyways, now we can actually change this button. The first thing we're going to change is the name. This would be the food button. Food. Or let's make it say Food Items.
Then right-click, build event. DoCmd.OpenForm "FoodF" and that's all you need, that's it. See? So simple. Who needs those wizards? You've just got to remember a couple of commands. Close it, open it again. I have a button up here on my Quick Launch Toolbar that runs that open main menu macro, which is this guy. All this does is open the main menu, but in order to put it up here on the Quick Launch Toolbar, it has to be a macro. Why? That's just how they did it. Access team, fix that too. I want to be able to put calls to some routines up here.
All right, there's my Fitness DB. We have to change the caption here too. Food Items, there we go. We're going to make a list of food list items and stuff like that too. We have so much coming, there's so much more to do.
Let's update this guy's caption. We'll make this the Food Database and our Food Database Fitness. This database. I'm into fitness - fitness whole pizza in my mouth. I don't know where that came from.
Version, we're going to go version 0.1 beta. Put the beta there. There we are, it's actually technically alpha, it's still in development, but we'll call it beta. Save it, close it.
We have to change this to the TechHelp Free Template. Let's go to File, Options, Current Database, and then right here, we'll put Fitness Database. All right, that's all we need for now. Hit OK.
I don't usually put version numbers up here. I put them on the main menu; otherwise, it gets too much data in multiple spots. I just want it in one place, so I know what the version is.
All right, so we're off to a good start. We're getting there. Now, we're going to start right off today with an extended cut for the members. I'm going to show you how to put a button on your food form to go out to OpenAI using the API, and then get those macros. You don't even have to copy and paste them from ChatGPT; it's going to happen right in your database.
We'll talk about that in the extended cut. Silver members and up get access to all the extended cut videos, not just this one. There are hundreds of them by now. So join today and get lots of extra cool stuff and free classes and my eternal gratitude.
All right, so that's going to do it for part two of the TechHelp video for today. What's up next? I don't know, I'm doing this as we go, making it up as we go along. We're going to have fun. I don't know what's coming in part three, we'll see tomorrow.
So that's it. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part three.
TOPICS:
Planning fitness database tables for food and meals
Building a food items table with macros and nutrition
Designing a table for meal groupings
Setting up a daily worksheet for meal tracking
Creating a meal plan with daily meals
Logging daily intake for tracking purposes
Using ChatGPT to generate nutrition data
Copying formatted macro data into Access tables
Manually entering food items and serving sizes
Building a single form for food items
Binding forms to tables in Access
Customizing form layouts and formatting
Adding and arranging macro fields on a form
Implementing a notes and URL field for foods
Creating a button to open URLs from a record
Using VBA's FollowHyperlink to open browser links
Updating main menu buttons to link to forms
Renaming and updating captions on forms and menus
Managing Access templates by removing unneeded objects
Setting version numbers and updating database properties
COMMERCIAL:
In today's video, we're continuing with part two of the fitness database series. You'll see how to set up tables for tracking food items, meals, meal plans, and daily worksheets, plus get an overview of how to organize exercise routines right inside Access. We will build a new food table, enter some example foods, and create a user-friendly data entry form with a special VBA button to open links to your favorite products. Along the way, you'll learn handy tricks for pulling nutrition data, customizing your forms, and updating your main menu. Also, in today's Extended Cut, I will show you how to use OpenAI's API to pull food macros automatically into Access, streamlining your workflow even further. 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 reason Richard Rost recommends planning your tables before building a database?
A. It reduces the database file size
B. It makes designing forms easier
C. It helps ensure all necessary data and relationships are captured effectively
D. It allows the database to run faster
Q2. In the context of the fitness database, what is the relationship between meals and food items?
A. Each meal contains only one food item
B. Meals are groups of food items combined together
C. Food items are groups of meals
D. Meals track daily calorie expenditure
Q3. Why does Richard prefer to include serving size in the food description field?
A. It simplifies form design
B. It eliminates the need for extra math during data entry
C. It allows for more complex queries
D. It helps Access calculate macros automatically
Q4. What is the main purpose of the daily worksheet in the fitness database?
A. To log exercise equipment used at the gym
B. To track daily meals and activities, and mark completion
C. To monitor total database users
D. To generate monthly invoices
Q5. When tracking nutrition in the food table, which of the following fields does Richard specifically mention tracking?
A. Sodium and cholesterol
B. Protein, calories, carbs, fat, fiber, sugar, and added sugar
C. Vitamin C and iron
D. Water intake only
Q6. Why does Richard avoid using the Hyperlink data type in Access for storing URLs?
A. It does not support long URLs
B. It cannot be searched efficiently
C. It is inflexible and causes problems in the database
D. It requires paid licensing
Q7. What VBA command does Richard use to open a URL from a form?
A. OpenURLBrowser URL
B. GoToWebPage URL
C. FollowHyperlink URL
D. LaunchWeb URL
Q8. Why does Richard leave the ID field off the main data entry form for food items?
A. Autonumbers are not used for tracking records
B. The ID field is only needed for advanced reports
C. It is unnecessary for most users and mainly used for teaching beginners
D. It slows down the form load time
Q9. What is the role of the StatusBox mentioned in the video?
A. To indicate if the database is trusted
B. To display custom messages within forms instead of using pop-up message boxes
C. To show Access system errors only
D. To hold temporary calculation results
Q10. How does Richard recommend a beginner should get familiar with the database structure before modifying the template?
A. By reading the Access user manual
B. By watching the corresponding TechHelp Free Template beginner videos
C. By directly editing all tables
D. By experimenting with SQL commands
Q11. Why are meal plans separated for different days in Richard's database design?
A. Because each day requires tracking different types of food items only
B. Different routines and calorie needs for different days require distinct meal plans
C. To limit database size
D. To allow users to print meal plans more easily
Q12. What advantage does prototyping your tracking system in Excel offer before moving to Access?
A. You can reuse charts and graphs
B. It allows you to identify table structures and necessary data fields ahead of time
C. It automatically converts data to Access format
D. It eliminates the need for databases entirely
Q13. What strategy does Richard describe for managing calorie surpluses using the database?
A. Ignoring calorie surpluses as long as protein is met
B. Calculating and suggesting extra exercise minutes to offset surplus calories
C. Deleting surplus entries from the log
D. Doubling the protein intake on surplus days
Q14. In the fitness database design, what is the functional analogy Richard draws between food items and exercises?
A. Both are irrelevant for building database tables
B. Both can be grouped into higher level structures (meals and exercise plans)
C. Exercise plans only track food
D. Food items are stored as exercise logs
Q15. Richard demonstrates how to import food data from ChatGPT into Access. What is a critical step mentioned for successful pasting?
A. Ensuring row shading matches in Access and ChatGPT
B. Matching the exact number of fields to the clipboard contents
C. Only using numeric values in each field
D. Compressing the table before pasting
Answers: 1-C; 2-B; 3-B; 4-B; 5-B; 6-C; 7-C; 8-C; 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.Summary Today's TechHelp tutorial from Access Learning Zone continues building the Fitness Database in Access. This is part two of the series, so if you missed the first installment, I strongly recommend watching it first to get the basic overview of the project.
Many times when I start designing a database, I encourage everyone to plan out their tables ahead of time. Luckily, since I have been tracking my meals and workouts in Excel for several months, I already have a solid prototype to work from. For example, I know I need a table to store food items. Each food item will have details like calories, protein, carbs, fat, and other macros. That is our first main table. I will also need a table for meals. Each meal is essentially a group of food items, such as a can of fish, a cup of rice, and a bag of mixed vegetables, all combined and named something like "Fish, Rice, and Veggies". With each meal, I can total up the calories and macros, and then group meals into meal plans.
Meal plans help me organize my eating by different types of days. For instance, on days with more cardio, my meal plan looks different than on strength training days. I track things like breakfast, snacks, lunch, and blocks of cardio, since I cannot always manage a long cardio session in one stretch. Instead, I break it up into smaller periods before and after dinner, and hopefully, as I get fitter, I will be able to increase the time spent on the bike.
To manage all this, I like to have a daily worksheet in the database - somewhere to plan or log each meal and workout as I go through the day. The worksheet lets me check off my meals and workouts, monitor how many calories I have left for dinner, and decide if I need to cut back or add some extra cardio to balance things out if I overeat. These daily logs will become a valuable tool for tracking progress. If I am not losing weight, for example, I can look back and see if I have been in a calorie surplus.
Along with food, the next step is creating tables and forms to manage workouts. Right now, I keep track of exercises and weights on a whiteboard, but that is not the most organized method. With an Access database, I can enter each exercise and the corresponding details - such as which body part the workout targets, max weights, and repetitions. Exercises can be grouped together in various plans, such as "leg day" or "push-pull day," depending on training goals. If your goal is fat loss, I have found a simple three full-body workouts each week, supplemented by cardio, works well. For muscle building, I typically shift to a push-pull-legs-core split. Regardless of your approach, the database will allow you to assemble different plans and apply them to your daily worksheet, keeping a clear record of everything you do.
As we set all this up, I will point out relevant prerequisite videos and resources as needed. Since nothing has been built in Access yet, we will go through it step by step together. We will use my TechHelp Free Template as a starting point because it already includes useful forms and a main menu, saving us some early setup work. However, we will strip out anything unnecessary for this project, such as tables for customers or contacts, and keep only what is relevant.
The template also contains a "Not Trusted" form that checks whether your database is running from a trusted folder. If not, it warns you upon opening. This is especially helpful because in the past, I received many emails from users whose downloaded databases would not open. Now, if the database is not in a trusted location, it informs you immediately so you can move it.
For our core work, the place to begin is creating the food items table. I am focusing on tracking calories and protein but will also include macros like carbs, fiber, sugars, and fat. If you are particularly concerned about specific nutrients like cholesterol or sodium, you can add fields for those as well, but I am keeping the table simple for demonstration purposes.
When describing a food item, I also include the serving size right in the description field. This avoids confusion about portion sizes. For example, if a cereal lists a half-cup as a serving but I always eat a full cup, I note "one cup" in the description and double the values for calories, protein, and so on.
Now, after setting up the table, I entered a real example, including macros and a URL link to the product (so when I run low, I can easily reorder it from Amazon or wherever I buy it). I recommend using resources like ChatGPT to obtain nutrition data; you can ask for the macros of various foods and get nicely formatted answers to copy directly into Access.
Sometimes, getting data in the right format can be tricky. A helpful Excel trick is to copy data, select the same number of Access fields, and paste them in bulk. Keep in mind that AutoNumber fields are primarily for internal database management. Do not worry about the sequential order.
After adding a few food examples (like bananas and tangerines), I created a form for easier data entry and review. For the food form, I usually design things so that it is visually simple: a clear description on top, calories and macros grouped underneath, and notes (with a link, if needed). I generally skip displaying the AutoNumber ID field on the form unless teaching beginners, since it is not necessary for day-to-day use.
When Access forms are unbound (not connected to a table), you cannot immediately add fields, so always check and bind your form to the table first. Once fields are added and formatted, you can arrange them however you prefer.
To make it easier to open purchase links directly from the database, I avoid using Access's built-in hyperlink data type, as that can be problematic. Instead, I set up a standard text field for the URL, then use a small amount of VBA code to allow the user to click a button and have Access open the web page in your browser. This uses the simple FollowHyperlink command.
After testing that everything works, I updated the main menu for the fitness database. You might notice that changing form labels directly in Access can sometimes resize the controls in unwelcome ways. It is better to update the caption property rather than modify the label directly. Also, I left the StatusBox in place for displaying messages, which I use instead of frequent pop-up dialogs.
With the menu cleaned up, I replaced the default menu options with a straightforward button for Food Items. A single line of code opens the food form, replacing the need for any Access wizards. The version is labeled as 0.1 beta, although it is more of an alpha build at this point.
Looking ahead, the next phase will include features such as integrating with OpenAI's API to fetch nutrition data for foods directly from inside Access, so you would not even need to copy and paste from elsewhere. This will be demonstrated in the extended cut, where I show exactly how to connect to OpenAI and retrieve macros programmatically.
All of this is still a work in progress, so more features and improvements will be added in future lessons. If you are a Silver member or above, you can access the extended cut video where I go in-depth on the OpenAI integration as well as hundreds of other extended cut tutorials.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Planning fitness database tables for food and meals
Building a food items table with macros and nutrition
Designing a table for meal groupings
Setting up a daily worksheet for meal tracking
Creating a meal plan with daily meals
Logging daily intake for tracking purposes
Using ChatGPT to generate nutrition data
Copying formatted macro data into Access tables
Manually entering food items and serving sizes
Building a single form for food items
Binding forms to tables in Access
Customizing form layouts and formatting
Adding and arranging macro fields on a form
Implementing a notes and URL field for foods
Creating a button to open URLs from a record
Using VBA's FollowHyperlink to open browser links
Updating main menu buttons to link to forms
Renaming and updating captions on forms and menus
Managing Access templates by removing unneeded objects
Setting version numbers and updating database properties |
