Fitness 66
By Richard Rost
12 days ago
Why Zero is Often the Wrong Default Value Setting In this video, we'll talk about why using zero as a default value when designing tables in Microsoft Access can often lead to bad data and misleading results, and why it's better to use null values. We'll also cover where the fitness database series is headed, focusing on tracking body composition, the basics of building new tables from scratch, choosing fields and data types, atomic data structure, and organizing your data for more useful charts and queries. Even if you aren't interested in fitness, the techniques shown can be applied to any Microsoft Access database project. This is lesson 66. MembersThere 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!
PrerequisitesRecommended CoursesUp Next
Keywords TechHelp Access, zero is often the wrong default value, default null vs zero, table design best practices, body composition tracking, fitness database series, body fat percentage calculation, tracking fat mass and lean mass, blood pressure fields, BMR, visceral fat, resting heart rate, Excel data staging, data import tips, atomic data fields, avoiding bad data, charting trends
Subscribe to Fitness 66
Get notifications when this page is updated
Intro
In this video, we'll talk about why using zero as a default value when designing tables in Microsoft Access can often lead to bad data and misleading results, and why it's better to use null values. We'll also cover where the fitness database series is headed, focusing on tracking body composition, the basics of building new tables from scratch, choosing fields and data types, atomic data structure, and organizing your data for more useful charts and queries. Even if you aren't interested in fitness, the techniques shown can be applied to any Microsoft Access database project. This is lesson 66.
Transcript
Today we're going to talk about why zero is often the wrong default value setting to use in your Microsoft Access table design. Plus, we're going to talk about where the fitness database series is headed, body composition tracking, and how to design some new tables from scratch.
Just like I always say, you do not have to care about fitness, body tracking, meals, workouts, or any of that stuff to get value out of this video. Fitness is just the backdrop. The real value is in the techniques I'm showing you, and they apply to any Access database you build, whether you're working with customers, inventory, orders, or anything else. You'll learn the skills you need to design a clean, professional-quality database and customize it to fit your own projects.
Let's get to it.
Up to this point in the fitness database series, we've spent a lot of time on the food side of things: logging meals, tracking calories, macros, quantities, all that. Now, we're going to start moving into the next big section of the database, which is tracking body composition and workouts.
Before we dive in, I want to reiterate something I've said throughout this entire series. You do not have to care about tracking your weight, workouts, body composition, or any of that stuff to get value out of this. The techniques you are going to see apply to any database: customers, inventory, scheduling, orders, projects, you name it. This just happens to be a convenient real-world example that lets us explore those ideas in a different way.
I've been wanting to build this database for myself and this gives me an excuse to do it with you guys as well. If you've been following me along so far, you've seen that throughout the food system I threw in all kinds of cool tips and tricks, so we're going to continue doing that.
I also want to frame why this part of the series matters, especially for those of you who keep asking me to spend more time showing how to build databases from the ground up. Lots of people send me emails or comments; they want to see my thought process behind table design. What tables do I need for this industry type or that kind of a database? Well, here's another example. We're going to talk about what tables we need, what fields belong in each table, and how you structure the relationships. That's going to be the focus for the next couple of lessons.
We're going to start with a simple one: a body composition tracker, things like weight, body fat, lean mass, and other measurements that you might get from a smart scale. I'll talk about which smart scale I use when we get to it. These things give us real-world examples of how to design tables properly from scratch. We'll talk through each field and why it exists, what's optional, and what you might change depending on your needs.
From there, we're going to move into workouts. That's where the database design really starts to get interesting. You're going to start dealing with multiple related tables: body parts, exercises, workouts, individual workout sessions, session exercises, lots of relationships to tie all that together. So I'm going to many, many-to-many relationships. Even if you don't care about fitness at all, this is a fantastic example of how to model a more complex system with multiple moving parts.
In fact, one of my students in the forums on my website just asked me about creating bundles of items. You have a bunch of raw materials and you want to bundle it together and sell that as a product. I'll cover that in I think Developer 30 or one of my higher-end developer lessons. He's nowhere near that yet; he's like in Developer Level Four. I said, but you know what? In the fitness database series, I do show you how to make meals that are comprised of multiple food items. Then you can add those to the food log, which is basically the same thing as adding products to an order. So it doesn't really depend much as far as what the database type is; it's the techniques that you learn to work with it.
Throughout this, I'm going to slow things down a little bit and walk you through my thought process. We'll start with the table design, often sketching things out in Excel first, like I usually do. Then we'll talk about the different field types, what belongs where, and why certain design decisions make sense. Then we'll move into the queries, sample data, some joins, and you can see how all the pieces come together. Then we'll build the forms on top of it.
Now, I'm not going to re-teach everything that we already covered in the food section, especially basic form design. A lot of that stuff I'm going to do off camera, and some of it's going to be members only because you've already seen those patterns. So the focus here is going to be on the new stuff: the structure, the relationships, the good database design. The free videos are going to lean more toward explaining the hows and whys, and then the extended cuts for the members will go deeper into the full walkthroughs and the implementation details. I'm not going to reinvent the wheel for you guys every single video. I might say, hey, we did this in the food log, we're going to do the same thing here, and then in the extended cut I might walk through it.
Also, there are still some food section tweaks coming. There are some little refinements I want to make as I use it every day. Sometimes I find little things I want to add, and those might show up in future videos. Some of those changes will be members only, but I'll try to keep you informed of any bigger updates or structural changes that I make on the food side.
With all that in mind, let's get started with the body composition tracker, and then we'll start building out the workout side of the database, the right way from the ground up.
First off, I want to share something that one of my Gold members, Peter, shared in the forum on the website. He's modified a little bit to keep track of needs for a diabetic, and he says for doctors a food log can be really helpful but difficult to do. He added carbs and sugar, as well as calories and protein. Like I've always said, add whatever things you want to track. If you care about fiber, make sure you get enough fiber. You can always add that. He also added a field to record morning finger blood tests, the prick blood test, which is going to be similar to what we're doing with the body composition. He added a chart showing the last 14 days, which I'll scroll down here and show you his chart, really good chart.
He shared a couple of other things down here. He's keeping track of that in the food log, but we're going to make a whole separate table to track body composition. We're going to put all that stuff somewhere different. However, we might use the food log to get a summary of our workouts, at least as far as the calories go. What I do, for example, is I keep track of all my food on here, and then I put my workout on there as negative calories. These are calories that I burned on top of my normal BMR that I would normally burn just existing. So if I do a heavy session, 30 minutes of weightlifting for a guy my size, it burns roughly 300 calories. Now, I don't say that I can eat 300 more calories that day, but it helps me to take it into consideration.
As far as tracking body composition, here's what I track. Just like for food stuff where you don't care about protein, you track whatever you want to track for your needs. I track my total weight, and then I have a smart scale, which we'll talk about in a minute, that tracks my fat mass and my lean mass. Then I can calculate my body fat percentage; the scale does it, but we're going to do it in the database. I also track my blood pressure, only once a month, though. I weigh myself before every workout, and I try to work out three days a week. While I don't obsess over the scale, I like to see the trends, and I put my little happy and smiley faces, and I've been doing it on my whiteboard in my gym, which is basically my garage. It's more gym than garage; the car is not going in the garage ever again, so it's a gym now. One of these days, maybe I'll share photos. It's just a simple way to set it. It's nothing really crazy.
I started off doing it. I was 316 pounds, and now I'm finally down to - well, this is an older picture - I'm finally down to 290. I've cracked 290, so I'm getting there. But I started just tracking the date, my total weight, my fat mass, and my muscle mass or lean mass, basically. What you want to see is the fat mass go down, and since I'm in a weight loss mode right now, you want to try to maintain as much muscle as you can. It's very hard to do both. It's very hard to lose fat and build muscle. You've got to pick one and optimize for it. What I'm doing right now is I'm trying to lose weight. I want to burn fat and keep as much muscle as I can.
I'm also going to be adding resting heart rate to the database for myself. My scale tracks that, and so does my smartwatch. I think that's good to watch moving forward too. Again, I want to emphasize, this could be inventory levels, stock prices, employee metrics, calibration readings from a machine you track. The fact that it's fat and muscle doesn't matter.
Let me take a minute to talk about actual fitness for just a minute, for those of you interested. If you care about either building muscle or losing weight, burning fat, I do recommend getting yourself a smart scale. This is the one I recommend. There's a link to get one if you want. That is my affiliate link from Amazon. It's about 40 bucks. I'll make like three cents if you buy one.
A smart scale is important, I think, because, A, it's good for seeing trends. Are you going up or down with fat or muscle? Plus, I know a lot of people tend to get discouraged. You're eating right, you're working out, you're lifting weights, and the scale isn't budging. But you might be building muscle and burning fat. Muscle is denser than fat. You could put on a pound of muscle and burn a pound of fat, and the scale doesn't move if you're just looking at your weight. So this lets you track if you're going in the right direction as far as fat lost goes.
If you don't want to get a smart scale, a better indicator is how your clothes fit. Are your jeans fitting better? Your shirts fitting better? You could be getting healthy, burning fat, building muscle, and your weight might not change. So that's enough of that.
Let's start with the body composition table. When we get into the actual workout stuff, we're going to load up Excel, because we're going to have a bunch of different tables and relationships and junction tables and all that. For just a body composition tracker, this is relatively straightforward. This is literally just going to be one table. In here, we're going to have our ID, so BodyCompID. You can write out Composition if you want to. I tend to abbreviate, just be consistent. When I save this, it's going to be the BodyCompT. I don't want to have to type composition everywhere in my code. No, that's just a pain.
What do we ask ourselves when we're starting a new table? We got our who, what, where, why, when. The who, we're going to get to that eventually. We're going to try to make this thing multi-user... maybe. I've been saying maybe since the beginning, because the more we add to it, the more of a project that's going to be. But I do want to do it. Whether it's an extended cut member thing or not, I'm not sure yet, but we'll do something.
So we're going to go right to the when. I like to put DateTimeStamp, or just TimeStamp, whatever you want to call it, and this will be a date. This will default to Now, right now whenever you create the record.
I'm going to put in what I care about: the total weight. That's going to be a number. I'm going to use Long Integer. I don't care about fractions of a pound. If you do, change that to Double. Remember, there are only two number types you really want to worry about: Long Integer and Double. That's it. Don't worry about all the rest of them.
Then I've got Fat Mass, again, a number. I've got Lean Mass. That's your muscle, lean mass number. Now, what about the body fat percentage? A lot of scales give that. Do we need to put that in here as a separate field? Think about that for a minute. Do you store a number that could be calculated easily? Because what is the body fat percentage? It's your fat mass divided by your total weight. So I'm going to store fat mass and lean mass, and I'm going to calculate my body fat percentage. Now, some scales do it the other way around. Some scales will calculate and give you your percentage, and then you have to reverse engineer. I prefer storing the actual fat mass poundage because you can calculate it better. You might have to change your data entry form if that's what your scale gives you. Your scale might say your total weight is 150 pounds and your 15 body fat, and then you've got to reverse engineer that. But you could just make your form do that and then save the value that you want to save. It's up to you. My scale gives me this, so this is what I like. We'll calculate that in the query when we get to it.
I'm also going to add in here resting heart rate. It's also a number, beats per minute. I'm going to track my blood pressure. I've been doing it monthly; I got one of those little, you know, a little portable blood pressure cuff things at Walgreens. Is it as accurate as your doctor's? No. Mine's always lower than my doctor's, and my doctor said I have what's called white coat blood pressure or something like that because when you're in the doctor's office, you're nervous, and it's always higher. It's always 10 points higher at the doctor's office. But again, it's for tracking trends. If yours is calibrating, your doctor reads it as... just keep an eye on whether it's going up or down.
Do we store blood pressure as one field or two? For those of you who don't know, your blood pressure is two numbers, usually 120 over 80. You've got systolic and diastolic. Anytime you think of two numbers, you want to have two separate fields for that. That's what we call making your data atomic. Every bit of data should be in its own field. You can make your data as atomic as you want to, but if you ever want to do calculations on it, like if you ever want to know if your systolic pressure is going up or down, it's best to put them in two fields. So I'm going to call it BP_Systolic (that'll be a number) and BP_Diastolic, like that.
Those are values I won't be typing all the time. It's not like the ID or the table name. You're typing in the table name or the ID a lot more than you're typing in just one of the random fields. You could go sys or diastolic, ah, that's good enough.
I also like to throw Notes in most tables, long text, and that's probably about it. Let's save it, close it, open it, and let's put some data in here.
Now, looking at it right away, all of these are defaulting to zero. Normally, that's how I start a lot of databases, but for this one, I think I'm going to make those default to null. Here's the reason why: I'm looking at my old data that I took. Here's my whiteboard again. I know that I started working out back in like May, or maybe even earlier, maybe even April. Not exactly sure, but I started at 316. Then, here, when I got into June, I started tracking my weight, my fat, and my muscle. Then I added more stuff onto it.
That brings me to another point. I want to also add in BMR and my visceral fat, which I forgot to do in the table. So we'll add those too. BMR is your basal metabolic rate. One of the nice things about a smart scale is that it can calculate your lean mass and your fat mass and all that, so it can tell you what your BMR is. Your BMR is how many calories your body needs at rest, even if you don't do anything, even if you're a couch potato all day. That's how many calories you need. Your brain needs to fire, your heart's got to pump, you've got to breathe. So right now, I'm down here, I'm at 2407. So I need 2400 calories a day just to survive, and my goal is to eat fewer than that. I'm trying to eat around 2,000 calories a day, so that deficit will come off as weight loss. You need 3,500 calories to lose a pound. Like I said, a pound a week is about reasonable. So we've got to add that.
Again, the reason why I want to have null values in my table is because I've got some dates in here. I don't want to lose this data, but I don't know what to put in for these. Null means I don't have that data, and there will be some days where I don't have that data. Maybe on one day I take my blood pressure, but I don't have the rest of these figures. I don't want them to be zero. That's going to skew my charts. That's going to have a zero on the chart. So there are a lot of times when you want these values to not have a default; to make that null. Null is good. Null means I don't have data.
What do I always say in my beginner classes? No data is better than bad data. There we go.
Actually, now that I'm thinking about it and I'm looking at this chart, I have been tracking my weight to the tenth of a pound. Do I want to keep doing that moving forward? Yeah, I kinda do, because sometimes you get that little bump, right? Like, yeah, so I went down half a pound. So I think I'm going to change these. See, this is part of the design process. Sometimes you think you need a certain thing, you think you need a certain way, and then you start actually working with the database and then I'm like, you know what, I want to make these doubles. Let's make them doubles. Very easy to do.
Don't use decimal. I have whole videos on why you don't want to use decimal. Just stick with double, trust me. And don't bother with singles. Singles are from a time when memory was expensive. Don't worry about singles. So these are all doubles. Good, good, good, good.
I'm going to also add BMR. That can be a number, long integer. I'm going to add in my visceral fat rating. This is also a number and this is of type double because that's got a fractional component as well. This number that a lot of scales calculate - my last scale calculated it too. Visceral fat, for those of you who don't know, is the fat around your organs. That's the stuff that's dangerous. You have belly fat, subcutaneous fat, and you have visceral fat, which is like on your liver and around your vital organs. That's the most important fat number you actually want to get down.
Now, this has a proprietary scale, if you look at this thing. I started at 34, and I'm down to 33, which is good. You want to see this number go down. It's not a percentage. It's whatever proprietary index they're using, but that's just to see a trend with that. You want to watch that trend line go down. There are a ton of other stats that this thing tracks that I don't really care to track. Maybe later we'll add them, but for me, this is perfect. I got everything I want here. Let's save this again, come back out here.
Now I can put my data in here. I'm going to type in just a few things. I'll probably add more later. You can see here on my thing: progress, progress, progress - big question - and I add four pounds. I'm surprised I only added four pounds. Then progress, progress, progress. Thanksgiving - boom - four pounds. And then I had a weekend in Naples and I had some surgery, and it's like every little bit of progress and then something happens like a holiday or a vacation to knock it. But the thing is to stay motivated. My personal rule is when I'm at home, I try to eat clean and work out as much as I can, and when I'm on vacation, I enjoy life. So that's how I... 80/20 rule: be as good as you can 80 percent of the time, enjoy yourself 20 percent of the time. For me, I'm a little stricter because I'm trying to get down to a goal weight. Once I hit that goal weight, then I'll be a little more lenient, but I'm the kind of person where if I don't work out and watch my weight, it just comes back on.
Let me put a row of records in here. Now see, I only have that weight from, I'm going to guess, let's put April 1st in here, and I've got 316.1. I don't have data for the rest of it. In fact, BMR has got a default value. We've got to take that out, and the visceral fat, we had to null those. Make sure you null those values. We don't want any value in here if we don't put it in, because later on when we do our charts, we can generate our charts and say, you know, if it's a null value, it won't show up on the chart instead of this dip where there's a zero. You don't want that zero there.
For things like a customer database, like phone number, don't just require your users to have to put a phone number in because they'll type in 5551212, and then you got bad data. It's much easier to go through your database and find no data than it is to find bad data.
I'm going to rearrange these so that they're in the order that they are on my whiteboard, so it's easier for data entry. Ooh, I can probably use ChatGPT to copy these numbers. Let's do that.
The first step is to order these guys in the right order. So I got visceral fat, I got BMR, then I have the date, then I've got my total weight, my fat mass, my lean mass. This stuff isn't in there yet. So this is the data here that I have in my chart that looks like that.
The next step is to screen capture this, and we're going to have ChatGPT OCR. All right, so I've copied it to my clipboard. I'm going to paste it into ChatGPT. That will be as specific as we can with our prompt: Please OCR (optical character recognition) the numbers on this chart and provide them to me in a format that I can copy and paste directly into Excel. Excel easily works better than Access. I need the visceral fat, BMR, etc. If data is missing like the first two rows, just leave it empty. Let's see what we get.
Analyzing the image takes a second, and there we go, it copied it for us. Did a pretty good job too. It's going to save me some data entry. Let's copy that, head over to Excel, and paste it in. Excel does a much better job of getting this ready for import. I love to use Excel as a staging location.
Once we're in here, now we'll go 'text to columns,' delimited on our comma, and there we go. There's the columns we want. Next, everything looks good. Number, date, and finish. There we go.
Now, I'm going to make sure these columns are in the same order as we have them up here. They should be; I rearranged these before to match the other thing. We're going to select them all here, copy.
Now here's the important part. A lot of people miss this one. Come back over to Access, and over here, make sure you select the exact cells that you're pasting into and then paste. Just like that. See how easy that was? You got to make sure that's the key: your selection has to match the columns that you've got over in the spreadsheet, and then it's easy just to copy and paste them all in. See how easy it was to take this picture of my whiteboard, have ChatGPT OCR it, put it in this format, drop it into Excel, and then bring it right over into Access. I love tricks like that.
Now, for my blood pressure, I've just been tracking it and keeping it in my little Notepad database that I got. So here's my data. I'm going to put that in there. There's only four records. If you only got four records, I'm just going to type them in. That's good enough. The dates don't have to be exact. I do it once a month, roughly, so that's good enough for now. Like I said, I just want to see trends moving forward. I don't have resting heart rate at all yet; I've got to start doing that. That'll be something I'll start keeping track of.
So I think that's about going to do it for today. We covered a lot of stuff. We got the table built. I know I did a lot of talking today, but we're going to get more into it as we move forward here. We've got to build a query to calculate our body fat percentage and start working on the form for this and then maybe throw together a little chart.
Before we wrap up, remember the big takeaways from today. Defaulting fields to zero can create bad data and misleading results. Null values often tell a more honest story. Good table design comes from thinking through what you're really tracking and how that data will be used later. Remember my saying: no data is better than bad data. And I don't mean Bad Data from Star Trek because that's his brother Lore, and we're not going to get into that. I think I need to put that on a t-shirt: no data is better than bad data.
We've also set the stage for the next phase of the fitness database, including body composition tracking and walking through how to design new tables from scratch the right way. Once again, this isn't all really about fitness. It's about solid database design techniques that you can apply to any Microsoft Access project, whether you're working with customers, inventory, orders, or anything else.
That's going to be your TechHelp video for today, brought to you by AccessLearningZone.com. Be sure to post a comment down below and let me know how you liked today's video or what you'd like to see covered next.
Live long and prosper, my friends. I'll see you next time.
If you enjoyed this video, hit that thumbs up button right now and give me a like. Also, be sure to subscribe to my channel, which is completely free, and make sure you click that bell icon and select 'all' to receive notifications whenever I post a new video.
Do you need help with your Microsoft Access project? Whether you need a tutor, a consultant, or a developer to build something for you, check out my Access Developer Network. It's a directory I put together personally of Access Experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mentioned in the video can be found in the description text below the video. Just click on that show more link right there. YouTube's pretty good about hiding that, but it's there. Just look for it.
If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, tables, all that stuff. It's over four hours long. You can find it on my website or my YouTube channel. I'll include a link below you can click on. Did I mention it's completely free?
If you like Level 1, Level 2 is just $1. That's it. It's free for members of my YouTube channel at any level.
Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut TechHelp videos, and there are hundreds of them by now. They also get one free beginner class each month. Yes, those are from my full courses.
Gold members get the previous perks plus access to download all of the sample databases that I build in my TechHelp videos. Plus you get access to my Code Vault, where I keep tons of different functions and all kinds of source code that I use. Gold members get one free Expert class every month after completing the Beginner series.
Platinum members get all of the previous perks, plus they get all of my Beginner courses - all of them from every subject. You get one free Advanced or Developer class every month after finishing the Expert series. You can become a Diamond sponsor and have your name listed on the sponsor page on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video, brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today.
Live long and prosper, my friends. I'll see you next time.
Quiz
Q1. Why is using zero as a default value in Access tables often not a good idea?
A. Zero can indicate missing or incomplete data, giving a misleading result
B. Zero automatically calculates body fat percentage correctly
C. Zero is always the most accurate default value for numbers
D. Zero improves query performance in Access
Q2. What is a better alternative to defaulting fields to zero in Access table design?
A. Use negative numbers
B. Use a random value
C. Use null values
D. Use the number one
Q3. What is the primary focus of using the fitness example in the database series?
A. Encouraging viewers to get fit
B. Demonstrating Access techniques that can be used for any database project
C. Building only fitness-related databases
D. Showing how to use smart scales
Q4. When designing tables for body composition tracking, which approach should you take if a measurement is composed of two numbers, like systolic and diastolic blood pressure?
A. Combine them into a single text field
B. Store both values in a single numeric field
C. Use two separate fields for each value
D. Ignore the second value
Q5. Why is it beneficial to make your data as atomic as possible in database design?
A. It makes your tables smaller
B. It enables more accurate calculations and analysis
C. It reduces the number of fields in the table
D. It saves your database automatically
Q6. What technique does the instructor use to speed up the process of importing handwritten or photographed data into Access?
A. Manually typing all the values one by one
B. Using ChatGPT to perform OCR, paste the results into Excel, and then import into Access
C. Using only Access to import images directly
D. Scanning with a barcode reader
Q7. When storing body measurements, why might you choose a Double data type over a Long Integer?
A. Double allows you to store fractional values for more precise tracking
B. Doubles use less memory than Long Integers
C. Doubles are faster to search with
D. Access cannot calculate with Long Integers
Q8. Which principle does the instructor emphasize regarding missing and bad data?
A. Bad data is better than no data
B. No data is better than bad data
C. All fields should be required
D. Default values do not affect data quality
Q9. What is the instructor's general advice for designing tables when tracking multiple different kinds of data, like food, workouts, or inventory?
A. Always use the same fields in every table
B. Think through what you are actually tracking and how the data will be used later
C. Place all information in one big table
D. Require every field to be filled in
Q10. How does the instructor recommend handling optional or unknown data when entering records?
A. Enter an estimated value
B. Enter zero for missing data
C. Leave it null to indicate unknown or missing data
D. Make up a random value
Q11. When is it appropriate to recalculate a value rather than store it directly in a table, such as body fat percentage?
A. Only if the value is not used often
B. If it can be accurately derived from other stored fields
C. If database performance is not an issue
D. Only if the calculation is too complex
Q12. What is the main advantage of using Excel as a staging area before importing data into Access?
A. Excel has better OCR capabilities than Access
B. You can clean, rearrange, and format data to match the Access table structure easily
C. Access cannot import any external data
D. Excel prevents data loss in Access
Q13. The instructor mentions that design changes often happen after working with real data. What is the best practice in this scenario?
A. Make the table once and never modify it
B. Adapt and refine your design as your needs become clearer
C. Ignore new requirements
D. Always start with the largest possible number of fields
Q14. Why does the instructor prefer storing values like fat mass and lean mass instead of just the calculated percentage from the scale?
A. The scale percentages are inaccurate
B. Storing component values allows more flexible and accurate calculations
C. It reduces storage space
D. It makes Access queries faster
Q15. What is one benefit of tracking fields such as resting heart rate or blood pressure in your database, according to the instructor?
A. They can be used for entertainment only
B. Tracking trends in these numbers can inform health or business decisions
C. They are required by Access to function
D. All databases must have health metrics
Answers: 1-A; 2-C; 3-B; 4-C; 5-B; 6-B; 7-A; 8-B; 9-B; 10-C; 11-B; 12-B; 13-B; 14-B; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's video from Access Learning Zone focuses on why zero isn't usually the best default value when you're designing tables in Microsoft Access. I'll also talk a bit about the direction of the fitness database series, specifically about tracking body composition and building new tables completely from scratch.
Even though this series uses fitness as its example, you don't need to be interested in tracking meals, workouts, or anything fitness-related to get value from what I'm teaching. The real point is to demonstrate best practices in Access database design, and these approaches apply regardless of the subject matter, whether your project involves tracking customers, orders, inventory, or any other data. My aim is to give you the skills for building your own professional-quality database.
So far, we've mostly worked with the food tracking side, covering meals, calories, nutrients, and quantities. Now, we're moving into the next phase: body composition and workouts. Before we get started, I want to remind you that the strategies I show here are universal—they apply to any database, not just fitness tracking. This is just a practical, real-world context for teaching these ideas.
Another reason this section of the series is important is that many of you have asked to see my entire design process, especially with new tables for a different area of the database. You want to understand how I decide what tables and fields to use, how to relate them, and why I structure things as I do. So, these next lessons will dig into all of that, step by step.
I'll start with a relatively simple example: a body composition tracking table, recording fields like weight, body fat, lean mass, and other stats, much like you'd get from a smart scale. When we get to workouts, the relationships get more complex—multiple related tables will be needed for body parts, exercises, workout sessions, and so on. That provides a great opportunity to discuss more advanced relational design, including many-to-many relationships, which are useful in any field.
This pattern is similar to building bundled products from raw materials—a topic a student recently brought up in the forums. I pointed out that making meals from ingredients in the food log is really the same technique as building product bundles in a business database. The skills you learn are the same, whatever data you're working with.
Throughout this process, I'll walk you through my decision-making. I often sketch tables out in Excel first, then decide on fields, types, and relationships. We'll look at how queries and sample data fit together, and then how to build input forms on top of it all. While I won't re-explain every basic topic already covered in the series, I will focus on what's new and essential for good relational database development. Deeper walkthroughs and full implementation details will be included in extended cut videos for members, but the free videos will give you the big picture and explanations of why certain choices are made.
There are also still a few tweaks coming for the food portion of the database. As I use what we've already built, I find little refinements worth mentioning. Significant changes will be discussed in future videos.
Now, let's focus on the new body composition tracker. To give you more perspective, one Gold member shared how he modified his tracking to support diabetic needs, recording not only calories and protein, but also carbs, sugar, and blood sugar readings. As I always say, you should track what matters for your specific goals. He also developed a two-week summary chart, which is a good way to keep tabs on recent trends. We, however, are going to keep these stats in a separate body composition table, rather than the food log.
For myself, I usually track all my food intake and then log my workouts as negative calories—these calories burned get subtracted from my total, giving me a fuller picture of activity on top of my basic metabolic rate. This method helps me understand how daily exercise contributes to my calorie burn without encouraging me to eat extra to compensate for exercise. But again, how you handle this is up to your needs and preferences.
When tracking body composition, here's what I personally log: total weight, fat mass, lean mass, and from those numbers I calculate my body fat percentage. I also track blood pressure (about once a month) and resting heart rate, using both my smart scale and smartwatch. All of these are just examples; you can adapt this approach to any type of data—whether it's stock levels, equipment calibration, or sales results—the concept is the same.
If you're interested in fitness, a smart scale can be helpful for tracking true progress, since muscle gain and fat loss may cancel each other out in terms of total body weight. This is why seeing more granular changes is better than just weighing yourself.
When it comes to actually designing the body composition table in Access, we'll include fields for an autoincrement ID, timestamp of the record, total weight, fat mass, lean mass, and resting heart rate, as well as blood pressure (split into systolic and diastolic for atomic design). It's important to store each individual value you want to analyze later in separate fields, making calculations or data summaries possible. I also generally add a notes field for anything extra.
One key point in today's lesson is about default values: when you create this table, you shouldn't set default values for fields like these to zero. A zero may suggest that the user's weight or blood pressure is really zero, which is both misleading and damaging for data analysis or charting. Instead, it's preferable to let these fields default to null, indicating that the information simply wasn't recorded for that entry. This approach preserves the integrity of your data, especially when it comes to trends and summaries.
As I looked over my historic weight log, I realized that I do care about tenths of a pound, so fields such as weight and fat mass are better set as Double data type in Access, not Long Integer. Use double for values with decimals. Also, avoid Decimal and Single types in Access; Long Integer or Double are almost all you need for everyday work.
Sometimes you'll want to add more measurements, like basal metabolic rate (BMR) or visceral fat score, especially if your smart scale provides those. Visceral fat is particularly important since it represents fat around your vital organs and is typically expressed as a specific index, not a percentage.
Again, some data from your devices may use proprietary scales, but the main goal is to see trends rather than dwell on the absolute numbers. For fields like blood pressure, always keep the two numbers in separate fields so you can analyze each individually.
When getting large amounts of old or handwritten data into your database, I often recommend using Excel as a staging ground. Take a photo or scan of your data—like a whiteboard chart—and use an OCR tool such as ChatGPT to convert that to a format you can paste directly into Excel, clean it up with 'text to columns,' then copy and paste into Access. When pasting, make sure columns in Excel match the fields in Access, including the order. This makes the whole importing process smooth.
Personally, I add or rearrange columns in the table as I go along, adapting the layout to match whatever order makes data entry more efficient. It's also common to have incomplete rows, especially when adding historical data. That's why setting the default to null is so helpful. Later, when building queries or charts, you can ignore missing data, and charts won't be thrown off by artificial zeros.
A general principle is that no data is better than bad data. For example, don't force users to enter a phone number if they don't have one, or they'll just enter fake data. It's easier to spot genuinely missing data (nulls) than to identify and clean up incorrect entries.
This adjustment and redesign is part of building any real-world database. As you use your system, you'll find what you need to change, and the best databases are those that are maintained and updated as requirements become clearer.
To sum up, today we've gone through the reasons why using zero as a default is a bad idea in most cases and demonstrated better ways to structure your tables, especially for tracking body composition and similar stats. We've set up the table with the right fields, types, and defaults, and discussed best practices for importing and managing your data. Remember—these database design principles apply to any application you build in Access.
You can watch a complete video tutorial that includes detailed, step-by-step demonstrations of everything discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Why zero is the wrong default value in Access tables
Using null vs zero for missing data
Building a body composition tracking table
Identifying and defining key fields for tracking health metrics
Deciding on data types for body composition fields
Storing calculated fields vs storing raw data
Making data atomic with separate fields for each value
Adding and organizing fields such as weight, fat mass, lean mass
Adding blood pressure as two separate fields
Adding BMR and visceral fat index fields
Adjusting field types from integer to double for decimal values
The process of refining table design through real use
Using Excel as a staging location for data import
Using ChatGPT for OCR to convert handwritten data to digital
Importing bulk data from Excel to Access tables
Ensuring matching column order for importing data
Data entry tips for batch record input
Explaining the impact of null vs zero on charting and reporting
Examples of practical naming conventions for fields and tables
Applying good database design principles beyond fitness databases
Matching table design to real-world data capture needs
Article
When you are designing tables in Microsoft Access, one common mistake is to use zero as the default value for numerical fields. This feels intuitive since zero is a valid number and most fields, like weight or price, seem to start at zero. However, using zero as the default can introduce significant problems in your data, cause misleading results in your analysis, and break the integrity of your database.
To explain why, let's look at a practical example: tracking body composition in a fitness database. If you assign a default value of zero in your table for fields such as weight, body fat, lean mass, or blood pressure, then every new row you create will automatically fill these fields with a zero even when you have not actually measured or entered a number. This means that if you forget to fill in a field, or if you are not tracking that value on a particular day, your database will contain zeroes. This looks like real data, but it is actually misleading. For example, if you are entering your weight after a workout and you do not have your blood pressure for the day, you have two choices: leave it blank, which correctly indicates you did not record it, or let it default to zero, which suggests your blood pressure is literally zero—a medical emergency, not a missing value!
The correct approach is to let untracked or unknown values stay blank. In database terminology, a blank means a value of NULL. Null is not the same as zero. Zero means you definitively recorded a zero. Null means no value exists for that record. Whenever you run reports, make charts, or perform calculations, using null values avoids artificial dips or spikes in your data: a missing data point simply does not appear, rather than being counted as a zero.
Let's translate this into a real technique. Imagine you are building a simple body composition tracking table. Typical fields might be: an ID (such as BodyCompID as the primary key), DateTimeStamp (the date and time the measurement is taken), TotalWeight (your body weight), FatMass, LeanMass, RestingHeartRate, BMR (Basal Metabolic Rate), VisceralFat, BP_Systolic, BP_Diastolic, and Notes. For most of these, you want the data type to be Number. The one exception is Notes, which should be Long Text. When you set up the Number fields, remove any default value of zero. In the field properties, just leave the Default Value property blank. This way, if you do not enter something, it stays null.
Think about what this does for you in practice. Suppose you weigh yourself and log your weight as 200.5 pounds, but you do not check your blood pressure that day. In the entry, TotalWeight contains 200.5, but BP_Systolic and BP_Diastolic are left blank. Later, when you chart your blood pressure over time, that day will not show up as zero—it just will not appear, giving you a clean, honest chart.
Likewise, when you design forms and queries, be sure to handle nulls gracefully. For calculations, you can use expressions that only operate on non-null values. For example, to calculate body fat percentage, if you have FatMass and TotalWeight, use an expression like:
BodyFatPct: [FatMass]/[TotalWeight]
If either value is null for a record, the result is null, and that record does not appear in your body fat percentage calculations.
You might ask whether you should ever store calculated values, such as body fat percentage itself, or whether you should just calculate it on the fly. In general, it is better to store the source measurements (here, FatMass and TotalWeight) and calculate the dependent value (BodyFatPct) in a query or on a form. This keeps your data consistent and avoids discrepancies if, for example, someone later updates your fat mass measurement.
Let's walk through a simple table design, as you would set up in Access Table Design View:
Table: BodyCompT
- BodyCompID (AutoNumber, Primary Key)
- DateTimeStamp (Date/Time)
- TotalWeight (Number, Double for decimals, Default Value: blank)
- FatMass (Number, Double, Default Value: blank)
- LeanMass (Number, Double, Default Value: blank)
- RestingHeartRate (Number, Integer, Default Value: blank)
- BMR (Number, Integer, Default Value: blank)
- VisceralFat (Number, Double, Default Value: blank)
- BP_Systolic (Number, Integer, Default Value: blank)
- BP_Diastolic (Number, Integer, Default Value: blank)
- Notes (Long Text)
As you use the database, you might realize you want to track more fields, such as BMR or visceral fat, and you can add them easily, making sure again that their default is blank, not zero.
It is essential to make your data "atomic," which means each field holds a single value. Do not store blood pressure as "120/80" in one field—use two fields, one for each number, so you can analyze them separately if needed.
When transferring legacy data, for example from a whiteboard or a sheet of paper with incomplete entries, you can use tools like Excel or even AI OCR (optical character recognition) to prepare your data for import. In Excel, arrange your data to match your Access table field order, then simply copy-paste from Excel into Access. Again, blank cells in Excel translate to nulls in Access, not zeroes.
To sum up, you should avoid defaulting to zero for numerical fields in Microsoft Access unless zero is always a meaningful value. Leaving numeric fields blank so they contain nulls is more accurate and honest. This practice avoids corrupting your analysis and makes your data easier to interpret, because missing data is explicitly missing, not misleadingly zero. As you continue building out tables—whether for fitness, inventory, sales orders, or any subject—always ask yourself whether blank or zero is the right default for each field. In most tracking and logging scenarios, null (blank) is best for any field you might skip or omit on a data entry.
Remember, no data is better than bad data. This principle will help you build cleaner, more professional, and more adaptable Microsoft Access databases for any project you pursue.
|
