Age Demographics 2
By Richard Rost
4 years ago
Age Groups in Access Using the Switch Function
In this Microsoft Access tutorial, I will show you another method for creating age demographic groups (brackets) using the Switch Function. This is an addendum to yesterday's Age Demographics TechHelp video.
Pre-Requisites
Recommended Course
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, Age Group Range Statistic, age demographics, demographic reports, age groups, age brackets, Group age data into age ranges, how to I make a query for age groups, Age Group, grouping
Intro In this video, I will show you another method for calculating age group demographics in Microsoft Access using the Switch function. We will talk about how to create age brackets such as "Under 18," "18-24," "25-49," and "50 and up," and organize them for analysis. I will walk you through building and sorting these groups in a query, and discuss the pros and cons of using the Switch function versus other methods.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
Today's tip is kind of an addendum to yesterday's TechHelp video, where I showed you one way to calculate age brackets from the demographics of your customers - like what their age is, and put them in a group, 18 to 24, 25 to 49, and so on. I mentioned in that video that there are a million ways to do this, and I gave you my preferred way. I also mentioned there was another way to do it with a Switch function. I think it is a little more difficult for a beginner, but if you know the Switch function, this could be easier and faster.
Let me start by saying this. First, if you want to know what I am talking about, go watch the age demographics video that I released yesterday, and that will give you all the basics. Second, if you want to learn about the Switch function, go watch this video. I have a video for everything. The Switch function is basically a way to avoid nested If functions. If you have multiple options, instead of having "If this, then if that, then if that," and so on, you can use the Switch function. Go watch that if you want to learn what the Switch function is.
Now that you are on board and know what all of that stuff is, let me show you how to use the Switch function to do what I did in the last video, but a different way.
Here is the video from yesterday. I did some interesting things with pie charts for the members. We went through modern charts, classic charts, and a whole bunch of different stuff. What we did is build this CustomerQ where we figured out the age and then set up different brackets for the groups by decade. This is fine. This is the way I have done it for years. There is a different way. There are a million ways to do everything in Access. I can think of four ways to do most things.
Let's copy this. Control-C, Control-V makes a copy of it. We will call it Customer2Q or whatever. We will just do it a different way. Ready? Design view. We need the age, but we can get rid of these brackets here. We are going to replace this with a Switch function. I am going to zoom in so you can see it - Shift-F2. This is what it looks like. I will call it AgeGroup.
We are going to put the Switch function in here. You have a condition and then a value if it is true, then another condition and another value if it is true, and so on. The Switch function drops out as soon as it finds one that is true. The first one that works is what you get.
We will start off from the bottom. Age is less than 18, comma, then you put in here what you want its group name to be. I am going to start it off with a one because when it comes to the aggregate query later, we are going to sort these. If you do not put a number or something at the front, it is going to sort in whatever numeric order you get, so I like to do it this way. So, "1. Under 18." Then comma, then your second group, just like I did in the Switch Function video.
Next, we will say if age is less than 25. Remember, anybody under 18 has already dropped out. The Switch function exits. Now we are going to get people that are 18 through 24 - less than 25. Comma, and then in quotes what you want it to say, "2. 18-24." Because remember, the other group is "under 18," so this does not include 18, so this one is 18 to 24. If you do not want to get confused, you could put "less than or equal to 17," whatever works for you.
Comma, what is the next group? Let's go, "age less than 50," comma, so this one will then be "3. 25-49." Then, everybody else, so "age greater than or equal to 50," comma, and then "4. 50 and up." Close the parenthesis.
I just think this is more complicated than what I did before, but it is a matter of opinion. Hit OK.
Especially for beginners - beginners look at the Switch function and get confused. The If function, for some reason, I think - and I have been teaching Access now for almost 30 years - when we get to the If function, people get it. Switch function can throw people. Save it and now run it, and there are your groups.
Let me sort it by this - right-click, sort. You can see here are all your "Under 18"s, group two is "18-24," and so on. See why I put the number in front of it. If you did not do this, you would get "18-24" first and "Under 18" would be at the bottom. You do not want to put "0-18" because it is kind of silly.
Now we just aggregate this like we did in the last class. All right, create a query design, bring in the Customer2Q that we just made, bring in AgeGroup twice, just like we did in the last video. Then we are going to turn on aggregating. We are going to group by, and then count. We are going to count the records this time. You cannot sum those up because it is a text field.
We want to sort, so we will sort this one ascending, and then run, and there you go. There is your age group and the count of the number of people that have that value in that field.
Again, there are some benefits to doing it this way, and some benefits to doing the other way. Just pick whichever one you think is easier. I do not want a barrage of emails and people going, "Oh, the Switch method is easier," and other people disagree. I am just - again, what did I say at the very beginning in the last video? Working with Access is like playing with Legos. My job is to show you all the different ways that these pieces go together. There are five different ways I can think of to do this.
I showed you guys one. I showed the members another way - we used a table so the user can change those age groups at the user level instead of having to go into the query design mode, because you never want your end users playing with your queries. You lock that stuff down. I have other videos on that.
But this is another method you could use if you want to get in there and build a query to do it. There are lots of different ways to put these Legos together.
So, there you go. There is your Fast Tip for today. I hope you learned something. See you next time.Quiz Q1. What is the primary benefit of using the Switch function in Microsoft Access as discussed in the video?
A. It allows you to bypass query security settings
B. It avoids the use of nested If functions for multiple conditions
C. It creates more efficient forms for data entry
D. It is required for all aggregate queries
Q2. When defining age brackets with the Switch function, why did Richard add a number at the beginning of each group label?
A. To make the labels easier to remember
B. To control the sort order of the groups alphabetically
C. To help with exporting the data to Excel
D. To avoid errors in the Switch function
Q3. According to the video, what happens when the Switch function finds a condition that is true?
A. It evaluates all remaining conditions
B. It stops and returns the corresponding value
C. It continues until the last condition is found
D. It produces an error if multiple conditions are true
Q4. In the example given, what would the "2. 18-24" bracket represent?
A. Ages less than or equal to 17
B. Ages 18 to 24, inclusive, after "Under 18" has been excluded
C. Ages 25 to 49
D. Ages 50 and above
Q5. Why should you not allow end users to modify queries directly, according to the video?
A. Users might accidentally delete important data
B. Users often do not have access to the tables
C. Query design should be controlled to avoid errors and confusion
D. It is more efficient for users to change forms instead
Q6. How does the Switch function process multiple conditions?
A. In random order
B. Starting with the condition with the largest number
C. Sequentially, returning the result from the first true condition
D. By combining all true conditions into one result
Q7. After setting up the age brackets using the Switch function, what should be done to count the number of people in each group?
A. Use the Sum function on the AgeGroup field
B. Group by the AgeGroup field and count the records
C. Filter out groups smaller than five people
D. Aggregate using the Average function
Q8. What alternative method was mentioned for allowing users to manage age groups without modifying the query?
A. Using a Switch function directly in a form
B. Creating a table for age groups for users to update
C. Giving users access to VBA code
D. Letting users edit reports directly
Answers: 1-B; 2-B; 3-B; 4-B; 5-C; 6-C; 7-B; 8-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 video from Access Learning Zone provides a Fast Tip that expands on yesterday's TechHelp lesson about how to calculate age groupings for customer demographics in Microsoft Access. Previously, I explained one method to determine age brackets, such as under 18, 18 to 24, 25 to 49, and 50 and up, and group your customers accordingly. In that video, I mentioned that there are many ways to approach this task and that you should use the method that works best for your needs. I also noted that it is possible to achieve the same result using the Switch function, which might be preferable for those already comfortable with it.
First, if you are not familiar with the basic age demographic setup, I recommend reviewing the earlier video for an introduction to that process. Additionally, if the Switch function is new to you, I encourage you to watch my separate tutorial that explains how it works. The Switch function is essentially designed to replace multiple nested If statements by providing a cleaner and more straightforward way to check several conditions in sequence. As soon as a condition is met, Switch delivers the corresponding result and exits.
Once you understand the Switch function, you are ready to see how we can apply it to categorize age groups. In the previous video, I set up a customer query to calculate age and assign each person to a specific bracket by decade. To demonstrate another approach, I create a duplicate of the original query and switch to design view. This time, I remove the fields used to define age brackets previously and instead, use the Switch function to assign an AgeGroup based on each customer's age.
The Switch function takes pairs of expressions and return values. For example, if the age is less than 18, it assigns the group "1. Under 18". The next condition checks for age less than 25, which catches everyone from 18 to 24, and labels them "2. 18-24", since the first condition already excluded anyone under 18. You simply continue this process for each age bracket you wish to define, finishing with a catch-all group for anyone 50 or older, such as "4. 50 and up".
I recommend starting each group label with a number. This ensures that when you later sort the grouped results, the age brackets appear in a logical order. Otherwise, text sorting could place "18-24" before "Under 18", which does not make sense in this context. Adding numbers to the group names keeps everything organized when you aggregate your results.
Once the query is set up, you save and run it to see customers categorized into their appropriate age groups. You can then create an aggregate query based on this result. Bring in your new query, add the AgeGroup field, and apply a count to see how many customers fall into each category. Sorting the results ascending by your numbered group labels produces a clear, organized output with the total count per age bracket.
There is no single right way to perform this task in Access. The method you choose depends on your level of experience, personal preference, and the needs of your database. Some may find the Switch function more confusing, especially beginners who are just getting used to the If function. Over my many years of teaching Access, I have found that everyone gravitates toward the techniques that make the most sense to them. My role is to provide you with different approaches so you can choose what works best.
I also show members how you can set up a table for these age groups, making it possible for users to modify them without working directly in the query design. This keeps your queries protected from accidental modification, which is important for maintaining database integrity.
This Fast Tip gives you one more tool for grouping ages in Access. As always, there are many possible solutions, just like assembling Lego pieces in different ways. Try out the methods and decide which one is best for your particular project.
If you want to see a full video tutorial with step-by-step instructions for everything discussed here, please visit my website at the link below. Live long and prosper, my friends.Topic List Copying and modifying a query in Access
Using the Switch function to group ages
Creating custom age brackets with Switch
Sorting age groups with custom prefixes
Aggregating data with group and count in queries
Displaying results with sorted age groups in Access
Comparing Switch and If functions for grouping data |
