Fitness 3
By Richard Rost
10 months ago
Food Group, Combo Box, Continuous Form, Query Alias
In this Microsoft Access tutorial I will show you how to add food groups to your fitness database, link food items to these groups, and create a continuous form for listing and filtering foods by group. We will cover table design, assigning foreign keys, setting up combo boxes, building queries, and organizing your form layout and properties for better usability. This is part 3.
Members
In the extended cut, we will remove the API key from the database and instead store it in a file for added security. I will show you how to update your code so that the API key is no longer exposed in the database itself.
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
Recommended Courses
Up Next
Keywords
TechHelp Access, fitness database, food groups, macros button, ChatGPT integration, continuous form, food group table, multi-user database, foreign key, combo box, query design, food group filtering, calories, protein, food list form, API key security
Subscribe to Fitness 3
Get notifications when this page is updated
Intro In this video, we continue building the Microsoft Access fitness database by creating a food groups table, adding a food group ID to the food table, and using a combo box to assign foods to groups. I'll show you how to join foods and groups in a query, alias field names for clarity, and design a continuous form for listing food items. We'll also cover sorting the food list, setting form properties to restrict edits, customizing labels, adjusting tab order, and launching the food list form from the main menu. This is part 3.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.
Today is part three of my fitness database. I'll repeat this: even if you're not into fitness and you don't care about tracking calories and workouts and all that, watch this series because I'm going to be covering a lot of cool fundamental database stuff. It's really for everybody - fitness is just the example.
If you haven't watched parts one or two yet, go watch those first, then come on back.
Last time, we got our fitness database started. We got our button here to open up our nice little single form. I'm going to move this over here and save it. In the extended cut, we made a little button down here to get macros. You type in the title of what you're looking for, hit get macros, and it goes out to the OpenAI ChatGPT engine and pulls back all of this stuff. We did that in the extended cut. That's really cool, so if you want to learn that, sign up as a member today.
But today we're going to do a bunch of other stuff. Let's start off by making a continuous form. This is OK so you can go through it like this, but I want to be able to see a nice list of them, and I want to add groups. Let's actually start with the groups.
I want to be able to put these foods in different groups. That could be breakfast, or cereal, whatever you want to make it. This could be fruit. We've got other stuff in here, beans, that kind of thing, whatever you want to put in - your meats.
So let's start off by making our food groups.
We're going to create a table in design. We'll keep it simple for now: food group, because we might have different groups later. So I'm going to call it food group ID. I don't know if someone's beaming in, I see. We'll have a description, and that's good enough for now. Let's save that. Let's call this the food group table.
What you put in here is really up to you. In fact, I thought about making this user definable because we are going to make this multi-user. But I think this is one of those things that might be better off just database-wide. But we might change that in the future.
So in here, what do we have? We've got dairy, fruits, vegetables, grains (like bread, bagels, that kind of stuff), protein - that'll be your meats, legumes, beans, peas, nuts and seeds, oils and fats (my extra virgin olive oil that I love so much), then whatever other stuff you feel like tracking in your database. If you want, you can put in sweets and desserts, if you have a ho-ho fetish and you love your sweets. Then beverages, if you have beverages that have a lot of calories you want to track. Things like condiments and sauces, it's usually negligible, you know, spices and seasonings.
I do like having a group in here for frozen dinners, because I love those. I love the Hungry Man frozen dinners. That's not the best food for you, but in a pinch, you just throw it in, it's like 500 calories. I don't eat the desserts though - their Hungry Man brownies taste like crap. I even emailed the company. I'm like, your food is amazing for a frozen dinner, but your brownie? No. It tastes like I'd rather eat cardboard.
I also like to track on here shakes. I like the Core protein shakes and my protein bars. I have a whole bunch of different ones. Like I said before, I'm on a quest to find the best one.
That's good enough. There's our food groups. So let's save that.
Then we're going to put the food items into a food group.
Now, I thought about making this one-to-many because some foods do fit into multiple groups, but I thought maybe it's best just to do it one-to-many for now to keep it simple. If we decide to do it later, we can always upgrade, and that will show us how to upgrade stuff too. Sometimes you start building your database one way and then it ends up going in a totally different direction and you have to gut stuff. I'm hoping not to have to do that, but we will if we need to.
So, now we go to the food table, and we'll add a food group ID. That's our foreign key - it's a number of type long integer. I like to keep all of my IDs, even the foreign keys, up at the top of the table, so we'll put it right after the primary key. We'll save it. Default value should be zero, but that's fine. That's good. So we'll close that now.
We have to assign groups to the food items that we have in here. We'll need a combo box to do it with. Let's put it right below the title. Oh my god, this is why it slides over here, because every time I work on it, it moves over. Let's shrink this up a little bit. I'm going to leave some more room for some different buttons and stuff down here. Let's pick the URL down a notch.
And we'll put the group in here for design. Give me my combo box. Drop you right there. Look up the values from a table or query. Where are we getting our data from? Food group T. Bring over both fields. Sort it by description. That looks good. Next, store that value in the food group ID of the food item table. Label: group, and so on.
Now that label comes in way over here. We're going to move it over there. Slide you back this way. This is where my OCD kicks in, because now these have to line up exactly the right way. So we're going to do that. We're going to make this that width. Let's do this: format paint, paste, and paste over that one.
I think we're good, and we didn't do our tab order yet. So, form design, tab order. I'm going to auto order it. Oh, it's combo 18. We have to remember to rename that. I like to go down this, and then we'll come back up here. So right now, combo 18, we're going to put that after added sugar, and those buttons won't be in the tab order. I don't like doing that. URL will go after that, and then the notes will go after that.
I like to take buttons out of my tab order. Tab stop: no.
Let's give this guy a good name. This will be food group combo. Save it, close it, open it.
So this will be in the grains category. What else do we have in here? Banana - fruit. There we go. I'm just going to copy that because I know there's more. Andrian - apple - avocado. Now here's where you get kind of iffy, because botanically avocados are a fruit, but nutritionally, I think this should be stored with oils and fats, because you're eating an avocado for its healthy fat content. It's up to you. It's how you want to do it.
Beans - legumes. What else do we have in here? Blueberries is fruit, paste. Cheese is dairy, and then of course, this is proof.
Looks like I didn't put the stats in here for chicken breast. Let's hit get macros, and there it is. See how cool that is? It goes out to OpenAI and gets the data for you. Sweet.
So we've got our groups in there now.
I'd like to do a continuous form like we have the customer list in the main database and the TechHelp free template. I want to be able to filter that based on the food groups. So I want it to say, show me all the dairy, and it shows you the dairy.
So let's start off with a query. Create query design. We're going to need our food group table and our food table. Bring both of those in there.
I want to see the description of the food group. Now, I've got two descriptions in here, so we're going to rename the first one food group. We're going to alias it over here. We will need the ID too, before I do it for filtering, so put that there. There's our food group description because I hate referring to it as food group T bang description and then food T bang description. I just changed the names, give an alias. Over here, we will need the food ID. We will need the description from this table. I'm going to call this the food description. Like that. Then whatever other fields you want to see in your continuous form, that's up to you. I want to see calories and protein, that's what I care about the most. The rest of it, I don't care. Bring in whatever you want to bring in.
Sorting it in the query isn't really meaningful because we're going to sort it in the form, but I'm going to save this as my food with group Q. All right, so food Q with the groups added into it. That's what it looks like.
Let's set up a continuous form now for foods. We're going with green, so we'll make this a slightly different shade of green. I'm going to copy my continuous form, copy, paste, we're going to call this our food list F. Right click, design view, and we're going to first bind the form to that query we just made - food with group Q. Save it.
Let's change the color. I like to open this guy up so I can see what color we got here. You came in really tiny, didn't you? Right click, we're already in design mode. Let's click here and go to format and make this a darker green for this one, and down here too. There we go. This could be a light that way. Perfect.
Let's bring in some fields. I'm going to get rid of these guys, goodbye. Let's go to our field list, add existing fields. Now we don't need the food group in here, but we're going to be filtering out in a minute. But I do want to see the food group down here, so we're going to see that first; get rid of the label.
Then I want to see the food description next to it, get rid of the label. You can use different modes to do that. I just like doing it by hand. I've been doing it by hand for years, just used to it. Then I want to see the calories and protein. Bring them in, get rid of their labels, and put them up here.
They don't need to be that big. If you have something with that many calories, you've got a problem. I like to use the single label for this, so we're just going to get rid of this one, stretch this guy across like that. It's a cheap little trick. You can just go group, then space, then food item, then space, then calories and protein. There you go. It got too wide. Perfect.
I like to leave a little bit of space there for the scroll bar.
Let's see what we've got. Save it, close it, open it. Looks pretty good. Move it over here, make it bigger. Save that.
This is the time when you make your little changes and you think, on second thought, I think we are going to sort this in the query. Let's do that. I was going to do a sort in the form, but let's start with the sort in here. I'm going to sort it by the food group description, and then sort by the food description. Let's do that. I was going to use an SQL statement in here, but I think for now this is fine. There we go.
Now, whether or not you want to allow people to be able to modify stuff on here, that's up to you. Me personally, I generally don't. I don't like giving people the ability to add, remove, and delete stuff from here. I like to force them to have to open up this guy. Unless you've got a situation where you're doing a bulk edit.
What I like to do in this case is come in here and set this guy so that allow additions is no, allow deletions is no, and allow edits is no. If you're going to do that, you might as well turn off the record selectors. The navigation buttons you can leave on if you want to, because that'll show people they can go down and see how many items there are.
Let's also set the caption here and say food list. Let's see what we're looking like.Oh, and let's make this button open that instead. All right, see, I put a message in here that my API key is in the database still. That is triggered when this thing closes because I keep flashing my API key on the screen, which means I then have to log on to OpenAI and change my API key again. But yes, it's okay to close.
In fact, in today's extended cut, we're gonna get rid of that. We're gonna take it right out of the database and put it in the file.
All right, so let's change just the food list. Right click, build event, and we just change this guy too. See, here's the code that checks the API key; we can get it that day later. Food list, save that.
In fact, members, while you're watching, we are gonna get rid of this because the API key is coming out. And I want to remember to get rid of that, so goodbye.
All right, save it, close it, and from the main menu, at the food list, there it is. This won't be open yet. You can see all your stuff.
Okay, now we need a way from here to open up to that. So I like a double click event. That's up to me, up to you, whatever you want to do. This one's up to me; what you do is up to you.
And we'll cover that as well as adding an add button and some other stuff in tomorrow's video.
So tune in tomorrow, same bat time, same bat channel, and members can watch it right now because I am gonna keep recording tonight. Even though I said last night I was gonna do that and I ended up not doing it, something came up and I couldn't, but tonight I got some time. So I'm gonna be recording.
So that's gonna do it for part three. Tune in tomorrow for part four. That's gonna be your TechHelp video for today. Hope you learned something. Live long and prosper, my friends.
I'll see you tomorrow for part four.
TOPICS:
Creating a food groups table
Adding food group ID to the food table
Creating a combo box for selecting food groups
Assigning food items to food groups
Setting up a continuous form for food items
Building a query to join foods and food groups
Aliasing field names in queries for clarity
Designing and formatting a continuous food list form
Sorting food list by group and description
Setting form properties to restrict edits
Adding a label and customizing the food list form
Binding the food list form to a custom query
Setting tab order for form controls
Opening the food list form from the main menu
COMMERCIAL:
In today's video, we're continuing with part three of building the fitness database. You'll learn how to set up food groups, create a new table for them, and add a group combo box to your food form. I'll show you how to tie foods to their groups, set up relationships, and organize everything using combo boxes. We'll create a continuous form to list your foods, sort and filter by food group, and customize which fields you see. You will also learn how to prevent editing in your list forms. Members can watch the extended cut for moving API keys out of the database file. 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 'food groups' in the fitness database?
A. To allow users to enter recipes for different meals
B. To organize food items into categories for better management and filtering
C. To count the calories of each group separately
D. To track expiration dates of food items
Q2. In the video, how is the relationship between food items and food groups initially set up?
A. Many-to-many, with a separate junction table
B. One-to-one, linking each food to a unique group
C. One-to-many, where each food is assigned to one group
D. No relationship is established
Q3. Which field was added to the existing food table to link it to the food group table?
A. Food description
B. Macros value
C. Food group ID as a foreign key
D. Food calories
Q4. What is the recommended data type for the 'food group ID' field in the food table?
A. Short Text
B. Currency
C. Date/Time
D. Number (Long Integer)
Q5. What user interface element is used on the food form to assign a group to a food item?
A. Option group
B. Combo box (drop-down)
C. Command button
D. List box
Q6. Why did the instructor suggest possibly changing the relationship of food and food groups from one-to-many to many-to-many in the future?
A. To allow tracking of food expiration dates
B. Because some food items can logically belong to multiple groups
C. To enable calorie counting for each user
D. To support multiple meal plans per day
Q7. How are food group options populated into the combo box on the form?
A. Typed manually into the property
B. Linked to a table or query containing all groups
C. Entered each time the form loads
D. Fetched from an online API
Q8. Why did the instructor choose to align and format elements on the form precisely?
A. To fit more records on a single page
B. To ensure the code runs faster
C. For visual consistency and user experience (OCD preference)
D. To prevent Access from crashing
Q9. What is primarily displayed in the new continuous form created during the video?
A. Only food group names
B. A list of food items with their group, description, calories, and protein
C. Exercise routines
D. Macro and micronutrient breakdowns
Q10. Why is sorting implemented in the query for the continuous form rather than in the form itself?
A. It is not possible to sort in forms
B. The instructor prefers sorting in queries for convenience and clarity
C. Sorting in queries is faster than in forms
D. Forms do not support multi-level sorting
Q11. What settings did the instructor change to prevent direct editing, adding, or deleting of records in the continuous form?
A. Set allow additions, allow deletions, and allow edits to 'No'
B. Locked the database with a password
C. Removed all navigation buttons
D. Made the form read-only in properties only
Q12. What is the primary function of the query named 'food with group Q'?
A. To delete food groups not in use
B. To display food items along with their group descriptions for filtering and reporting
C. To create summary reports only
D. To count the number of macros in each group
Q13. Why might the instructor want to remove the API key from the database and store it in a file instead?
A. To make it easier to share the database with others without exposing the key
B. To speed up the performance of API calls
C. To reduce the storage requirements of the database
D. To allow everyone full access to the API
Q14. What feature did the instructor suggest adding in the future to increase navigation and usability within the database?
A. Advanced reporting with charts
B. Double-click events and an 'Add' button
C. Automatic spellcheck for food names
D. Barcode scanning capabilities
Q15. What was the main lesson emphasized about database development during the design of this module?
A. Databases should never be changed once built
B. It is common to redesign and upgrade databases as needs evolve
C. You should design databases specifically for fitness only
D. Table relationships are fixed and cannot be modified
Answers: 1-B; 2-C; 3-C; 4-D; 5-B; 6-B; 7-B; 8-C; 9-B; 10-B; 11-A; 12-B; 13-A; 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 focuses on the next phase of building our fitness database using Microsoft Access. Regardless of whether you have an interest in fitness or tracking workouts, I encourage you to watch this series because the concepts and database techniques we cover are valuable for many different types of projects. Fitness is just serving as our example here.
If you have not already worked through parts one and two of this tutorial, I recommend you review those first, as the material builds step by step.
Previously, we established the foundation of our fitness database and created a form you can use to view and manage individual records. In the extended cut of that lesson, we also added a button that allows you to retrieve nutrition macros using the OpenAI ChatGPT API. That feature is demonstrated in the members-only extended lesson, so if you are interested in integrating automation like that, you might consider joining as a member.
In today's session, we will take some important steps to enhance our database. The single form interface for reviewing foods works, but it is more practical to browse a list of items. Additionally, it makes sense to organize foods into groups such as breakfast, fruit, cereal, or any categories you might find useful. Our first task will be to establish these groups.
To accomplish this, the first thing I do is create a new table dedicated to food groups. For now, we'll keep it simple: a food group ID as the primary key, plus a description field. Later, if we need to expand the concept, we can. My intention is for food groups to be defined at the database level rather than per user, though we can revisit that choice later if needed.
Once the table is in place, you can define as many groups as you like. Some good examples include dairy, fruits, vegetables, grains (think bread and bagels), protein (usually meats, beans, peas, nuts), oils and fats (like olive oil), sweets and desserts, beverages, condiments, and even frozen dinners if you want to keep track of those. I personally appreciate a category for frozen dinners, because even though they are not the healthiest meals, they are convenient, and I like to know how many calories I am consuming. Likewise, I track protein shakes and bars to compare their nutritional content, since I am always searching for options that fit my goals.
With our food groups defined, the next step is to link each food item to its group. Technically, you might want to allow for a many-to-many relationship since some foods can fit into more than one group, but for simplicity's sake, we'll start with a one-to-many design. That means adding a new field to the food table for the food group foreign key. I prefer to keep foreign key fields near the top of my tables, right below the primary key. For each food item, you'll set the appropriate food group value, leaving flexibility to adjust or upgrade the structure later if required. Database development often involves rethinking structures as requirements change, and when the time comes, we can walk through the necessary revisions.
To make group assignment easy, we'll add a combo box to the form for editing food items, placing it beneath the title field. This combo box will list all the food groups from our food group table, display the description, and save the chosen group in the food record. For cleanliness and ease of use, I adjust the layout so the controls are neatly organized and set the tab order so users can move logically from one field to the next when entering data. I also make a habit of renaming controls to something meaningful, like "food group combo," and removing buttons from the tab order to streamline data entry.
After organizing the form layout, it is time to assign each food item to its corresponding group. For some foods, there may be multiple valid groupings. For example, an avocado is botanically a fruit but nutritionally considered a source of healthy fat. This part of the process is flexible and depends on your goals for categorization.
With item categorization underway, I want to introduce a continuous form for viewing a list of foods, similar to how customer lists are displayed in the TechHelp free template or the main database. This form will also make it possible to filter the list by food group.
To set this up, I first create a query that joins the food items to their groups, displaying each food along with its group, calorie, and protein data. I use aliases to clarify which description is for the group versus the food item itself. Sorting can be handled in the query, organizing items first by group and then by name, although further customization can occur later in the form if desired.
Next, I design a new continuous form based on this query. I style it to fit the theme of the database, select which fields to present, and arrange them for clarity. I prefer a single label spanning multiple columns for cleaner display, and I ensure there is space for navigation controls.
Once the form is ready, I consider access controls. I generally set it up so that users cannot add or delete records from the list view; instead, edits should occur through the individual item form. This approach helps maintain data integrity and prevents accidental changes. You should set permissions to your preferences, depending on whether bulk editing is required.
I also make sure to provide a way for users to open the item details form directly from the list. I typically use a double-click event to make this intuitive. There are other options, so choose what works for your application.
One important point covered in the extended cut of this tutorial is moving sensitive information, like your API key, out of the database and into a more secure file instead. Members can follow detailed instructions on how to accomplish that.
Looking ahead, I will add features such as an add button and additional enhancements in the next part of the series. Members can watch the next lesson right away as I continue recording more content.
That covers everything for part three of our fitness database project. If you would like to follow along with a complete video tutorial, including step-by-step demonstrations of everything discussed in this article, you can find it on my website at the link below.
Live long and prosper, my friends.Topic List Creating a food groups table
Adding food group ID to the food table
Creating a combo box for selecting food groups
Assigning food items to food groups
Setting up a continuous form for food items
Building a query to join foods and food groups
Aliasing field names in queries for clarity
Designing and formatting a continuous food list form
Sorting food list by group and description
Setting form properties to restrict edits
Adding a label and customizing the food list form
Binding the food list form to a custom query
Setting tab order for form controls
Opening the food list form from the main menu |
