Fitness 41
By Richard Rost
7 months ago
Combine Meals & Foods Together with a Union Query
In this Microsoft Access tutorial I will show you how to use a union query to combine food items and meals into a single combo box for your fitness database. You will learn how to add an IsActive field to the meal table, update related queries, and set up an alias for easier combo box searches. This is part 41.
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, union query, combo box, merge food items and meals, copy item button, is active field, meal table, meal list query, sum of calories, sum of protein, standalone queries, SQL view, query aliasing, food with group query, mealUnionFoodQ
Subscribe to Fitness 41
Get notifications when this page is updated
Intro In this video, we continue building the Microsoft Access fitness database in part 41 by creating a union query to combine the food and meal lists into a single combo box for easier selection. I will show you how to add and use an "is active" field in the meal table, update your queries to include this filter, and create an active meals query. We will set up field aliases for clarity and walk through using SQL view to build the union query needed to merge food and meal data together. This is part 41.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost. Today is part 41 of my fitness database series.
Like I always say, this is a database video, not necessarily a fitness video, so I am teaching database stuff. Today is really cool stuff. We are going to be doing a union query to merge together the list of food items and the list of meals, so we can put them together in one combo box.
You do not have to pick a different box. You can just pick from the whole list. You will see all your meals, all your food items, all your stuff. This works, like I said, with any database you can think of.
If you have not watched parts one through 40 yet, you have some watching to do. Go watch those, then come on back.
All right, folks. We are back, and we are going to start meals today because I promised in the last class we were going to do meals. I wanted to add something off camera that I really, really wanted. I will have to save this for after meals to show you what I did, but I added a copy item button.
A lot of times what I like doing is going back to previous days. Let us say here is my snack. Let us say I have the same exact snack today. I do not want to copy the whole day, but I want just these two items right here. Watch this. Click, click. I just copied those two items to today. There they are. Is not that cute?
That is a whole lesson. I added it to my database. I promised we would start meals, so we are going to save that for later. There is a little teaser. There are a couple of times where I am like, I just want to copy this one thing that I had three days ago. I do not want the whole day.
All right. I have my data in a separate one now because I have been going back and forth. Let us go back to your copy of the database, which is exactly the same except that button. We will add that button after we are done with meals.
After long ado, I know I have been hinting at it and talking about it, and last time I promised we are going to start meals today. We are. Instead of doing two boxes down here, one for just food items and one for meal items, we are going to put them all in the same box. We are going to use a union query to join those together in the same box.
As I mentioned yesterday, if you have not watched my union query video yet, go watch that now because I am not going to re-explain everything about union queries.
Now, before we add the meals to that form, I noticed a few things about meals. First of all, with the meal table, we do not have "is active" in here like we do in the food table. We have "is active," and the point of this is so that we can keep meals in there, but if, let us say, you decide you are not going to have this dish for a while or forever, like me, I switch sometimes. If I am in a cut mode where I am trying to lose weight, I have certain meals that I like, and if I am in a building mode, trying to build muscle, I have higher calorie, higher protein meals. We want that "is active."
I am going to copy that field and we are going to drop it in here and then save it. Then we have to make stuff active. I am going to make three of these things active so we can double check and make sure.
Let us say the fruit bowl is not active. On the form, we will just drop it down here somewhere in the bottom. Form design, add fields, is active should pop up right there. We will drop you down on the bottom and slide it over here. Make it so we can actually see it and read and write. Even though the name of the field is "is active," I like to call it "active" there.
Save that, close it. Is it working? Yes. Good.
Now, we have already got a meal list query (queue), which has the sum of the calories and the sum of the protein for that meal. We are going to use this for the combo box, but we have to add "is active" to it now. Bring in "is active." That should not change anything because it is still grouped by the meal stuff. It is still grouped by this and just summing up these. Adding that to this query will not change anything, because if it is the same for this and this, it will be the same for that.
Save that and run it. I am very cautious about adding new stuff to queries that I know are already used in other places. Be careful about that, but this does not affect anything.
Now, we can use this to make another query that is just active stuff. You can do some of this just in the SQL, but sometimes I find it is easier to just make a standard query for it. That is the benefit of making standalone queries. I do not try to shove everything into SQL or my VBA, especially for stuff I know I am going to use in several places.
We are going to create a new query. We are going to bring in that meal list query. That guy. Turn that off.
I am going to bring in the meal ID. Now for description, I am going to make this have the word "meal" after it, just like we did with the food groups like "dairy" and "carbohydrates" and "protein." We will do the same thing with meals so we can distinguish these as meals in the list. Then we could search for just "meal" when we are typing in the combo box. It will show all the meals.
I am going to zoom in so you can see this better. Let me get my zoom box really big. Let us bring that out a little bit.
Let us call it meal name in this query. That is an alias. We are going to do the description, put a quote space and then "meal" like that after it. So it will say "Rix Yogurt meal." This is what it will look like in the box. So I can type in "meal" and it will show up everywhere.
Back to design. A couple more things. We have "sum of calories," "sum of protein." I like that, but I am going to rename these as well, just so it is easier in the other form. We are just going to call this "Calories," and we are just going to call this "Protein." If you are tracking all the other stuff, put that in here as well. Whatever you want to track. Me, I just care about those two things.
Let us save this guy as "my meal list active q." It is just active items and that is going to show up. We did not put the criteria on. We need "is active," and I put stuff with criteria toward the front right after the ID. Criteria here is going to be "True," so it will not show up in the combo box unless it is marked active.
Perfect. Save it. Close it. We are ready to move on.
Now we have got our meal list. What is it? The food log? No, it is not the food log. What is the other one that feeds the other thing? Oh, the "food with group queue." What query is that? I forget.
This guy, "food with group active only q." So this guy and this guy, no, no, the meal list active. That is why you have to keep this stuff straight. I like to put this stuff on the screen while I am working with it. It makes it easier to see.
I want to union these two queries together so I get one set of data. The ID, the name, the calories, the protein.
Let us create a query. Now, a union query is one of the types of queries that you cannot use the graphical designer for as I explained in my video, the union query video. Right click and go to SQL view. This is where it is handy to know a lot of SQL. I am going to zoom in a little bit, so I will just use the zoom window. Shift F2.
I am going to move it down so I can see the fields. The first one is going to be:
SELECT mealID, mealName, Calories, Protein FROM mealListActiveQ
So I am going to call it union, so merge that with
Now, the field names have to match exactly. It will keep the names of the first query in here. That is okay. It does not really matter for the purposes of filling a combo box because it just goes by the columns.
So this is going to be
SELECT foodID, Description, Calories, Protein FROM foodWithGroupActiveOnlyQ
Save this. Oh, cannot save from there. Hit save. We are going to call this our "mealUnionFoodQ." Let us run it. Look at that. Everything is merged together. You can see there you go. You have your food items and your meal items together.
Now, I do not want people looking at this, people meaning you in the future or whoever else might be working on this with you, to think that these are all meals and meal names and stuff. We are going to come in here. We are going to alias just the first guy. We are going to call mealID as ID, because there are two kinds of IDs in there, which is a problem we will have to deal with in a few minutes. We will get to that.
Then mealName, let us call that as Description. The calories and protein are fine, that is the same. Now when I run that, I get this. It looks a little better. Save it. Close it. We are done with those guys too. Get rid of that, get rid of that.
So we have our query all set to go. We have the cool little two-ring symbol there for the union query, that is nice. Now we are all ready to take this union query and replace what is in this box with that. There are some issues we will have to deal with, and we will deal with all of that in the next class.
Let us see, today is Wednesday, the 24th of September, 2025. Tomorrow is going to be a non-fitness Access video or something else. I have a couple things planned. I am not sure what yet. We are going to pick this up next Monday, the 29th.
All right. That is going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you next time.
TOPICS:
Creating a union query to combine food and meal lists
Adding an "is active" field to the meal table
Updating queries to include the "is active" field
Filtering queries to show only active meals
Creating an alias for meal names with the word "meal"
Renaming summed fields for readability
Creating an active meals query
Building a union query using SQL view
Matching field names for union queries
Aliasing IDs and descriptions in the union query
Merging food and meal data for a combo box
COMMERCIAL:
In today's video, we are continuing with part 41 of the fitness database series, learning about how to merge your food items and meals into a single combo box using a union query in Microsoft Access. I will show you how to add an active status to your meal table, update your queries to show only active records, and create a union query to combine meal and food data for easy selection. You will learn how to set up aliases in your queries to keep your fields straight and get practical advice on organizing your database efficiently. You will 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 purpose of creating a union query in this video?
A. To combine meal and food item lists into one combo box
B. To filter food items based on calories
C. To merge multiple forms into one interface
D. To display only inactive meals
Q2. Why does the instructor add an "is active" field to the meal table?
A. To group meals by calorie level
B. To mark which meals are currently used and filter inactive ones out
C. To allow meals to be duplicated
D. To sort meals alphabetically
Q3. What advantage does adding "meal" after the description in the query provide?
A. It filters out food items automatically
B. It distinguishes meal items from food items within the combo box
C. It calculates protein totals more easily
D. It allows the combo box to sort by meal
Q4. Why does the instructor prefer to create standalone queries for certain tasks?
A. It makes queries run faster
B. The graphical query designer is always required
C. Standalone queries are easier to reuse in multiple places
D. You cannot use SQL for anything important
Q5. What must be true about the field names and order when performing a union query?
A. The field names can be different as long as the order is the same
B. Both field names and order must match exactly
C. Only the order matters; names do not
D. It does not matter as long as both are not empty
Q6. What is the primary data source for the combo box after changes in this tutorial?
A. The meal table only
B. The food log table
C. The union query combining meal and food data
D. The form design view
Q7. Why does the instructor alias columns like mealID as ID and mealName as Description in the union query?
A. To hide the original names from users
B. To make the combo box's required fields uniform regardless of the source
C. To prevent duplicate rows
D. To reduce storage requirements
Q8. Which operation is NOT performed in this video tutorial?
A. Renaming columns for clarity
B. Adding new fields to a table
C. Creating a graphical query with drag-and-drop
D. Using SQL view to write a union query
Q9. What criterion is used to filter for active meals in the active meals query?
A. Calories greater than zero
B. Protein less than the average
C. "Is active" equals True
D. Description contains 'meal'
Q10. In the context of this tutorial, what is a primary reason for querying only active records?
A. To make the application run faster
B. To ensure inactive meals and items do not appear in user selections
C. To save disk space
D. To allow unlimited combo box entries
Answers: 1-A; 2-B; 3-B; 4-C; 5-B; 6-C; 7-B; 8-C; 9-C; 10-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 is part 41 of my fitness database series. As always, my primary focus here is on Microsoft Access database development, using the fitness template simply as an example to teach useful database skills.
In this lesson, I am going to demonstrate how to use a union query to merge a list of individual food items with meal records, allowing you to present both in a single combo box. This way, there is no need to have two separate selection boxes for foods and meals; everything will be available in one convenient list. This approach is extremely flexible and can be applied to many kinds of databases, not just fitness tracking.
Before we dive into the technical work, if you have not watched parts one through forty of this series, it is a good idea to go back and cover those lessons first to make sure you are up to speed.
Now, let's continue. As I promised in the last class, we are going to start handling meals. I also added a feature off camera, a "copy item" button, to make it easier to duplicate specific foods from previous days to the current day without having to copy an entire day's log. While this is a handy feature, we will revisit it after we finish constructing the meal functionality; consider it a little preview of what is to come.
Because I have been making some changes in my test copy, I will return to your standard database copy so that you can follow along and not miss any steps. Once we finish the meals section, I will show you how to add the "copy item" button as well.
Since I have been hinting at introducing meals for a while, let's proceed. The goal is to eliminate having to use two different combo boxes at the bottom of the form, one for foods and one for meals. Instead, I want everything in a single box by merging the two lists via a union query.
If you are not already familiar with union queries, I recommend reviewing my union query tutorial first. I will not be going back over the general principles behind how union queries work in this lesson.
Now, as I was setting up the meals, I noticed that our meal table was missing an important field: "Is Active." The foods table already has this field to indicate which items are currently in use or available. It is useful for cycling meals in and out of availability. For instance, when I am trying to lose weight or gain muscle, I may want certain meals to be active or inactive depending on my nutrition goals. To bring meals in line with foods, I copied the "Is Active" field onto the meal table and updated a few sample records to reflect various statuses, such as making the fruit bowl inactive.
Next, I added "Is Active" to the meal form itself so that it is easy to view and edit in the interface. I prefer to label it simply as "Active" for clarity.
With the field in place, it is time to update our underlying queries. There is already a query that calculates the sum of calories and protein for each meal. It now needs to include the "Is Active" field as well. Adding this extra field does not impact the groupings or the sums, so everything should still work as it did before.
After saving those changes, I created a new query designed to show only active meals. Although you could filter records directly in SQL, I find it simpler and more reusable to create standalone queries for this sort of filtering, especially if you will be referring to the same set of records in multiple places throughout your database.
When constructing the query, I decided to give the description a twist: for meal records, I appended the word "meal" after the description (for example, "Rix Yogurt meal") so you can quickly spot or search for meals in the final list. This is helpful when using the combo box, as you can type "meal" to bring up only meal options.
I also took this opportunity to simplify the field names further, renaming the sums to just "Calories" and "Protein" for clarity and ease of use elsewhere in the database. If you track additional nutrition data, this is a good spot to include those fields as well.
I named this new filtered query "MealListActiveQ" to make it clear that it only includes active meals. I added the necessary criteria so that only records marked as active will appear in our results.
At this stage, I have two filtered queries: one for active meals (MealListActiveQ) and another for active food items (FoodWithGroupActiveOnlyQ). My next step is to combine these with a union query, presenting a unified list of both foods and meals for the combo box.
To do this, I created a new query in SQL View, since the union query type cannot be built using the graphical designer in Access. The two component queries must have the same number of fields in the same order, and ideally the same names, since the column names from the first part of the union define the resulting field names overall.
After setting up the query, I saved it as "MealUnionFoodQ." When run, it neatly displays both meals and food items together, easy to use in a single combo box.
To make the data even clearer and avoid confusion, I applied aliases so that both types of records would be presented under the same field names like "ID" and "Description." The only slight wrinkle here is that both meals and foods have their own separate IDs, but we will handle any related challenges in the next lesson.
The union query now serves as a comprehensive list you can draw from in your form's combo box, containing both food and meal items side by side.
That wraps up today's lesson. I will be returning with more soon, including how to connect this new combo box to your form and address any remaining issues with IDs and related data.
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 Creating a union query to combine food and meal lists
Adding an "is active" field to the meal table
Updating queries to include the "is active" field
Filtering queries to show only active meals
Creating an alias for meal names with the word "meal"
Renaming summed fields for readability
Creating an active meals query
Building a union query using SQL view
Matching field names for union queries
Aliasing IDs and descriptions in the union query
Merging food and meal data for a combo boxArticle Today, I want to teach you how to use a UNION query in Microsoft Access to merge two different lists into a single combo box on a form, which can make your database applications much more user-friendly and flexible. The example we will use comes from a fitness database, where you might have one table of individual food items and another table of meals, with each meal being a combination of those foods. Instead of asking the user to select their food or meal from two different combo boxes, we will combine both lists into one, so you can pick from everything at once.
Before we get started, one thing I always recommend is to make sure your tables are structured similarly if you want to merge their data using a UNION query. In my example, the food table already has a field called isActive. This field is a Yes/No value that lets us mark items as active or inactive, which helps keep the list tidy, especially if you later decide you do not want to see something on your selection list. It is a good idea to add an isActive field to your meal table as well, so both tables can be filtered the same way.
Once you have the isActive field set up in each table, be sure to update any forms you use for data entry so the isActive checkbox is visible and can be edited. It is helpful to call it Active on the forms, even if the actual field name is isActive, for simplicity.
The next step is to create or update any queries that you use to display food or meal options. For example, you might already have a query that lists meals along with the total calories and protein for each one. Add the isActive field to the query, but do not worry, as long as you are just grouping or summing the same fields as before, adding this extra field will not change your totals or breakdowns.
Now, let us make sure we can filter for only the active items. I suggest making a new query based on your meal list query that only returns the active meals. To do this, include the isActive field, and set the criteria to True so only records marked as active are shown. For clarity, give this query a meaningful name, like myMealListActiveQ.
It is also helpful to adjust the display of your query fields. For example, if you want to make it easier to search for meals in your combo box, you might want to append the word meal to the end of each meal description. You can do this using an expression in your query design grid:
MealName: [Description] & " meal"
You can also clean up your column names for calories and protein for ease of use elsewhere:
Calories: [SumOfCalories]
Protein: [SumOfProtein]
At this point, you have a query with just the active meals, each with a clear description, calorie, and protein count. Repeat a similar step for your food items table: have a query that shows only active foods, with clear names and all the same fields you want to merge with the meals query. For this example, let us suppose your active food item query is called foodWithGroupActiveOnlyQ.
Now you are ready to combine both lists using a UNION query. Unlike other types of queries in Access, a UNION query cannot be built using the design grid; you have to go into SQL View. To do this, go to the Queries section, select New Query, and switch to SQL View. Your UNION query will look something like this:
SELECT mealID AS ID, MealName AS Description, Calories, Protein
FROM myMealListActiveQ
UNION
SELECT foodID AS ID, Description, Calories, Protein
FROM foodWithGroupActiveOnlyQ;
Notice that the field names must match in both SELECT statements, and you use AS to give a common alias if necessary. In this case, I am standardizing the primary key fields as ID and the name/description field as Description.
Save this query as mealUnionFoodQ, or any friendly name you prefer. When you run it, you will see both your meals with meal names and food items with descriptions, all in one dataset. This is perfect for populating a combo box on your data entry form.
One thing to be careful about: both meal and food items have their own IDs, and there may be overlap. You will need to address that later when handling selections from the combo box, but for the purpose of building the list, this approach works effectively.
With your mealUnionFoodQ query built, you can now set your combo box's Row Source property to this union query. When a user clicks the combo box, they will see both food items and meals in one list, with the meal names conveniently labeled.
That wraps up the process of merging two different lists into a single selection control using a UNION query in Access. Once you get comfortable with this approach, you will find countless places where combining similar data makes your database forms more powerful and user-friendly. If you want to do more, such as identifying which type (food or meal) was selected, or handling the ID conflicts, there are additional tricks you can use, but this gives you the foundation you need to get started. |
