BMI
By Richard Rost
4 years ago
Calculating BMI (Body Mass Index) in Access
In this Microsoft Access tutorial, I'll teach you how to calculate Body Mass Index (BMI). We'll also talk about what BMI is, who it's good for, when it's not accurate, and I'll show you how to use DMax and DLookup to display the description that corresponds to that BMI number such as underweight, normal, overweight, and obese.
Recommended Course
Links
 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, calculate your bmi, Adult BMI Calculator, Body Mass Index Calculator
Subscribe to BMI
Get notifications when this page is updated
Intro
In this video, I will show you how to calculate BMI (body mass index) in Microsoft Access by setting up fields for height and weight, creating a query to perform the BMI calculation using the imperial system, and formatting the results. We'll also build a lookup table to categorize BMI results as underweight, healthy, overweight, or obese, and use DMax and DLookup to retrieve these descriptions in your query results. This tutorial provides a practical example of handling calculated fields and lookups in Access.
Transcript
Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I am going to teach you how to calculate BMI, or your body mass index, in Microsoft Access.
When I get questions from my students, especially my members, I put them in a big folder and keep track of them. When I see the same questions come up over and over again for multiple students, it piques my attention. A bunch of people recently have asked me how to use Access to calculate BMI.
For those of you who do not know what BMI is, BMI is a simple, inexpensive way to gauge a person's overall weight, from underweight to obese. It is just a simple calculation based on your body weight divided by your height. It does not take things like fat or muscle into consideration. That is one of the reasons why I personally say BMI is not valid for everyone. It is not valid for children, the elderly, bodybuilders, or athletes. Bodybuilders, for example, can have a lot of weight due to muscle mass, but the calculations will show them as obese when they are actually in excellent shape.
I actually went through something like this myself about 10 years ago. I was big. I was 340 pounds. I spent the year getting in shape and doing some weight lifting and I lost about 140 pounds, which was great. But I put a lot of muscle mass on and the BMI index was still saying I was overweight. I am like, no, that is not good.
There are definitely more accurate means of calculating your body composition, such as skin-fold thickness testing with calipers. They can do underwater weighing or bioelectrical impedance. There are lots of different ways to see how much of your body is fat versus muscle. In fact, I used to have a scale that you would step on. It would pass a current through your body up one foot and down the other basically, and it could calculate what percentage of your body weight was fat.
Basically, BMI is really only used as a general guideline for average adults. If you are an athlete, bodybuilder, really old, or a child, it does not work for you. But for most of us, it is okay.
So how do you calculate BMI? Well, it is basically a simple math problem. If you are using metric like most of the world, I do not know why here in the US we are not, it is your weight in kilograms divided by your height in meters squared. Height in meters is squared.
If you are using the imperial system like we are here in the United States for whatever reason, it is your weight in pounds divided by your height in inches squared, times 703. That is the conversion unit to go from here to here. So you have to multiply by 703.
Now you will get a number. Here is the range. You will get anywhere from about 18 or so as underweight all the way up to 30 and above as obese and here is a little chart.
If you want to learn more about BMI and how it is calculated, there is a link to the CDC's website. Of course, government websites can never make a short URL, so I will put a link to that down below. You can just click on it in the link section.
So let us go over to Access and see how we can calculate this.
Here I am in my TechHelp free template. It is a free database. You can download a copy up on my website if you want to. I am just going to use the CustomerT. Let us say that our business is personal fitness training. So for each of my customers, I want to track their height and weight.
Down here on the bottom, I am going to put the height in inches. I am going to do inches, using the imperial system. Metric is just as easy. This is going to be a number; long integer is fine. Number of inches. So if they are 5 foot 10, that is 70. Then we are going to do the weight in pounds and that will also be a number.
Alright, we will save that CustomerT.
Now let us go ahead and throw a query together. We will do our calculations in a query. Remember, we are not going to save that BMI in our table. We are going to use a calculated query field. If you do not know what a calculated query field is, go watch this video. You will find a link to it down below. Click on it, watch that, and then come back.
In my query, I will bring in my CustomerT, close the table stage here, and just bring down their customer ID, height, and weight.
Now, right over here is where I am going to calculate their BMI. I am going to zoom in, Shift+F2 so you can see it better. I am going to make the calculated field name BMI: That is the name of the field we are making. It is going to be their weight in pounds, [WeightPounds], divided by their height in inches squared.
To square something, use the caret symbol and then a 2. That squares it. Then that whole thing has to be multiplied by 703. That is the conversion. Are you worried about which goes in what order? That is okay. Remember our order of operations, our math rules.
Exponents go first, so that is fine. We do not need any parentheses here. This should calculate first and then PEMDAS or BODMAS or whatever you learned: parentheses (we do not need), exponents, multiplication and division. It will go left to right. So this will divide by that and the whole thing will get multiplied by 703. If we had some addition in here, we might need to worry about parentheses, but this is okay. We are good.
Let us save this as MyBMIQ query and give it a run. Now I am going to get nothing in there because there is no data in there.
Let us put some data in here. Let us say this guy is 65 inches high and 150 pounds. There should be a BMI.
I only want to see two digits, though. That is kind of crazy. Let us go back into design view. I am going to right-click on you, go to properties, and set format equal to 0.00 or it comes in as fixed. You can put two decimal places in there if you want to.
Now we run it and that looks a lot better.
Let us do a couple more. Let us say this guy is 70 inches tall, or 5 feet 10 inches, and still 150 pounds. 150. There we go. A little bit lower BMI because he is taller.
How about 70 and 90 pounds? That is pretty light. How about 70 and 200 pounds? How about 70 and 350 pounds? Now we are getting up there.
You can see there is our BMI.
Now, it would be nice to have the description right here to explain what that is - whether it is healthy, underweight, overweight, obese, etc.
Let us make a little lookup table and bring it over here.
To do this, we are going to need two more functions: DMax, which says go to a table and bring the maximum value back with a specific set of criteria, and that is a close cousin of DLookup. If you have never used either of these before, go watch DLookup first. It is the granddaddy of the other D functions. D stands for domain, or wherever you can look up values in a different table or query. Go watch DLookup first, then watch DMax, then come back here.
Before we can do some looking up, we have to have a table to look up our stuff in. So let us make the BMI table that describes what these things are.
Create table design.
BMIID. That will be our autonumber.
The BMI number itself. This will be a number. I will make this a double because we are going to have fractions in there, for example, 29.5.
Description.
And that is it. Save it. We will call this the BMIT or table.
We are going to say here zero. Zero is underweight. Zero means you do not exist, right? That is underweight.
Zero to 18.5, 18.5 to 24.9, and so on. We will use these numbers and fill this into our little chart table there.
The next one up is going to be 18.5. That is going to be healthy. That is the lower range of being healthy. If you are below that number you fall in the previous category.
25 is overweight. The next one, 30, is obese. On that chart they had 35 as very obese or morbidly obese, whatever they call it.
There is our lookup table. Close that.
Let us go back to our BMIQ. This guy. Design view. What we have to look up is:
Let us go back here. Let us take a peek at this so it is easier to see them both at the same time.
I have to take this number right here, this 24.96, and go to this table and say, okay, which value in here is the largest value in this column that is less than me? The largest value in this column that is less than 24 is 18.5, so that person's healthy.
Same with the 21.
What about the 12? The largest value that is less than 12 is zero. That person is underweight.
What is the largest value? Let us change this guy just a little bit. Let us make him 250. So he is 35. Let us get him to 220. Now that is still too much. Let us make him a little taller. Let us make him 80 inches tall. No, that is too much. Let us go 75 inches.
Good enough. Now I am 70 at 220. I want to be just standard obese.
What is the largest number in this column that is less than 31? That would be 30. See how that works?
We are going to use DMax to find the largest value in that column that is less than the number we are dealing with.
Hope you got it.
It is kind of like VLOOKUP in Excel. If you are familiar with Excel at all, you can have VLOOKUP look up a value in a range in a column, for example, and bring back the number that it fits. I use it for doing letter grades. For example, if you got a 62, it brings back that you got a D or an F or whatever that comes out to be. 85 is a B.
In this video, I show you how to duplicate that in Access. There is no VLOOKUP function in Access, but it is a combination of DMax and DLookup, which is what we are going to do right here.
Right here, zoom in, Shift+F2. I am going to look up the BMIID from the BMI table that is the largest one less than the current BMI value for this customer.
DMax: I am looking up the BMIID from the BMI table where the BMI value is less than or equal to the current customer's BMI. That is a bunch of craziness there.
Hit OK and then run that. There is the ID. You are going to get an error here because this person does not have any information. There is a bunch of stuff we could do with NZs and IIf errors and all that, but just deal with it. I have many other videos to explain how to do that.
Just put a value in there for them. If you want something in there like 50 and 150, now he has a value. He is pretty fat.
Anyway, 75. I am fat again now, so I can make fat jokes.
Back here. Well, I am pleasantly plump as I like to say.
Now I have got the ID from that table. Now, all I have to do is DLookup the description from the BMI table that matches this ID.
That is going to be Shift+F2. We will call it Description here. That is going to be DLookup.
What am I looking up? The description from that table where the BMIID equals the BMIID I just looked up a second ago in the previous column. Since that one goes first, that will work.
Save it. Control+S. Run it. There you go. There is our calculated BMI. The whole value is in there, even though we are only displaying part of it. You can round that off if you do not want that to happen.
Design view. Instead of displaying it with just two decimals, you could take the whole thing and round it.
Let me hit Escape. I accidentally did you see what happened there. I was typing and I was overtyping. If that happens, just hit the Insert key on your keyboard.
We can round that whole thing to two decimal places. That way, when I click in here, that is the actual value in there.
Learn about rounding numbers. I have a video about it. Want to hear? Here it goes. There is my rounding video. Again, links are down below.
Once again, that is the calculated value based on these numbers. You take this, go to this table, and say, okay, what number in that column is the largest number that is less than 24? That happens to be this one, which is ID2. That is the DMax. Then we DLookup ID2 to get healthy. See that? There you go. That is how you do it.
Now you can throw this into a form or do whatever kind of cool stuff you want with it.
There you go. That is how you calculate your BMI, and that is your Fast Tip for today. I hope you learned something, and we will see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault, where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free Expert class each month after you finish the Beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free Developer class each month after you finish the Expert classes. These are the full-length courses found on my website, not just for Access, too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond Sponsor and have your name or company name listed on a sponsors page. They will be shown in each video as long as you are a sponsor. You will get a shout-out in the video and a link to your website or product in the text below the video and on my website.
Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free.
Quiz
Q1. What does BMI stand for in the context of this video?
A. Body Mass Index
B. Basic Muscle Indicator
C. Body Measurement Information
D. Basal Mass Input
Q2. Why is BMI not a valid measure for everyone?
A. It requires expensive equipment
B. It does not account for fat or muscle
C. It needs professional medical examination
D. It is only for children and elderly
Q3. Which groups of people is BMI NOT appropriate for?
A. Children, elderly, athletes, bodybuilders
B. Average adults
C. Office workers
D. Teenagers
Q4. What is the metric calculation formula for BMI?
A. Weight in kilograms divided by height in meters
B. Weight in kilograms divided by height in meters squared
C. Weight in pounds divided by height in inches
D. Weight in pounds divided by height in meters squared
Q5. When using the imperial system, how do you calculate BMI?
A. Weight in pounds divided by height in inches
B. Weight in pounds divided by height in inches squared
C. (Weight in pounds divided by height in inches squared) times 703
D. Weight in kilograms divided by height in inches squared
Q6. In the Access example, why are height and weight stored in the table and not the BMI value?
A. Height and weight are harder to change
B. BMI is a calculated value and should be determined in a query
C. BMI requires manual entry
D. Tables cannot save numeric values
Q7. How do you square a number in an Access query formula?
A. Use the asterisk (*) operator
B. Use the circumflex (^) symbol followed by 2
C. Use the division (/) symbol
D. Use the plus (+) symbol
Q8. What is the order of operations as mentioned in the video?
A. Multiplication, Addition, Parentheses
B. Exponents, Multiplication/Division, Addition/Subtraction (PEMDAS/BODMAS)
C. Addition, Exponents, Multiplication
D. Division, Parentheses, Subtraction
Q9. Why format the BMI value to two decimals in the query?
A. To reduce file size
B. To make the result easier to read and more useful
C. To enable sorting in forms
D. To ensure accuracy for calculations
Q10. What does the BMIT table in Access store?
A. Only BMI numbers
B. BMI ranges and their corresponding category descriptions
C. Only customer IDs
D. Only height and weight data
Q11. What function is used to find the largest value in a column that is less than a given value?
A. DLookup
B. DMin
C. DMax
D. Sum
Q12. How does the DMax function help categorize a BMI value?
A. It finds the BMI description directly
B. It finds the maximum BMI value equal to or less than the customer's calculated BMI
C. It adds all BMI values together
D. It only looks at the minimum BMI
Q13. What is DLookup used for in the BMI calculation process?
A. To insert new BMI values
B. To retrieve the BMI ID
C. To look up the category description for a BMI range
D. To delete existing BMI records
Q14. If you wanted to round the BMI value to two decimal places in Access, what would you use?
A. The Format function
B. The Round function
C. The DMax function
D. The DLookup function
Q15. What is an analogy given in the video for the process of categorizing BMI values in Access?
A. Calculating interest in a bank account
B. VLOOKUP function in Excel to find letter grades
C. Sorting customer names alphabetically
D. Printing a report
Answers: 1-A; 2-B; 3-A; 4-B; 5-C; 6-B; 7-B; 8-B; 9-B; 10-B; 11-C; 12-B; 13-C; 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 video from Access Learning Zone will show you how to calculate BMI, or body mass index, using Microsoft Access.
Whenever students, especially members, send me questions, I keep a record of them in a big folder. When I notice a particular topic getting asked by multiple people, I know it's something that probably deserves a video. Lately, quite a few people have been asking about calculating BMI in Access.
First, let's discuss what BMI is. BMI is a quick and inexpensive way to estimate a person's general weight status, ranging from underweight to obese. It's just a straightforward math calculation based solely on your body weight and height. However, BMI doesn't account for the difference between muscle mass and fat. That's why BMI often isn't suitable for everyone. Children, the elderly, athletes, and bodybuilders are some notable examples where BMI may not be accurate. For instance, bodybuilders tend to weigh more due to muscle, and BMI may put them in the "obese" category even though they're actually in excellent physical shape.
I have some personal experience with this. About ten years ago, I weighed 340 pounds and then lost 140 pounds in a year, mostly through exercise and some weight training. I built a lot of muscle, but even after the weight loss and muscle gain, my BMI was still labeling me as overweight when, in reality, I was in pretty good condition.
If you want more accuracy in body composition measurements, there are better options out there, like skin-fold caliper testing, underwater weighing, or bioelectrical impedance. For example, I once had a scale that used a small electric current to estimate body fat percentage. These techniques give you a better picture of your actual body fat versus muscle percentage.
In most cases, though, BMI is a decent general guideline for the average adult. Just remember, it's not meant for kids, athletes, bodybuilders, or seniors, but it works fine for most people.
So how do you calculate BMI? It's a straightforward formula. If you use the metric system (which is standard everywhere except the United States), BMI equals your weight in kilograms divided by your height in meters squared. For those using the imperial system, like here in the US, you take your weight in pounds, divide by your height in inches squared, and then multiply by 703 to make the formula work with imperial units.
The result is a number that generally falls between about 18 (underweight) and 30 or higher (obese). There are established ranges and categories, and I include a chart for reference. If you want more on the science or official definitions of BMI, the CDC has a good page for details, and you'll find a link to it down below.
Once that's clear, let's move over to Access so I can show you how to set up BMI tracking.
I'm starting with my free TechHelp template, which you can download from my website. For this example, let's say we run a personal fitness business and want to store each client's height and weight in our Customer table. I'll add two new fields – one for height in inches and the other for weight in pounds. Both fields are numeric.
After saving the table, it's time to build a query for our BMI calculation. Remember, we don't want to store the BMI itself in the table. Instead, we want to create a calculated field in the query. If you're not familiar with calculated query fields, I recommend going over the relevant tutorial, which you'll find linked below.
In the query, I include fields for customer ID, height, and weight. The new field we'll calculate is BMI. The formula uses the customer's weight divided by their height squared and multiplies that result by 703 (for calculations using imperial units). Just be sure you're following the correct order of operations in your math. Exponents come first, followed by division and then multiplication, just as you may recall from school.
I'll name and save this query as MyBMIQ. When you run it, initially you won't see any values until you add some data to the table. For example, if someone is 65 inches tall and weighs 150 pounds, you'll see a calculated BMI once you enter those values. To make the output more readable, you can set the field's format to show two decimal places.
You can now try a variety of height and weight combinations to see how the BMI changes.
It's also useful to provide a description indicating whether each BMI value falls within underweight, healthy, overweight, or obese categories. For this, let's set up a lookup table. We'll need functions like DMax and DLookup. If you're new to these, check out the DLookup tutorial first, as it lays the foundation for understanding the domain aggregate functions in Access.
Now, we'll make a BMI table to hold the BMI categories. Each entry in this table includes a unique ID, the minimum BMI value for the category (as a double, to allow fractions like 29.5), and a description (such as "Healthy," "Overweight," etc).
Fill out this table with the standard BMI ranges. For example, under 18.5 is underweight, 18.5 to 24.9 is healthy, 25 to 29.9 is overweight, 30 and above is obese, and so on.
Now, back in our query, we'll use DMax to find, for each BMI, the highest minimum value from our BMI table that is less than or equal to the client's BMI value. This works similarly to a VLOOKUP in Excel with a range lookup option, where you return the highest matching value in an interval.
Once you have the ID from the BMI lookup table that matches the client's BMI, you use DLookup to pull the description that matches that ID.
This way, your query will display not only the calculated BMI but also a brief descriptor indicating the BMI category for each client. If you want to manage the way the BMI values display, you can round them to two decimal places.
The basic workflow is: calculate BMI using height and weight, use DMax to find which BMI category the result falls into, and then DLookup to get and display the corresponding category description.
Once you have this set up, you can build it into any Access form or use it as needed in your application.
That's how you calculate BMI in Microsoft Access, and that's your Fast Tip for today. If you want a complete step-by-step video tutorial where I walk through everything in detail, you can find it on my website at the link below.
Live long and prosper, my friends.
Topic List
Introduction to BMI and its calculation principles
Differences between metric and imperial BMI formulas
Adding height and weight fields to Access tables
Creating a calculated BMI field in a query
Using math operations and order of operations in queries
Formatting BMI results to two decimal places
Creating a BMI category lookup table
Entering BMI ranges and descriptions in the lookup table
Using DMax to find the correct BMI category
Using DLookup to display BMI category descriptions
Rounding calculated BMI values in queries
|
