Fitness 67
By Richard Rost
10 days ago
This Function is Missing Right Where You Need It
In this video, we'll talk about calculated fields, table-level validation rules, and creating reliable data entry in Microsoft Access, all demonstrated with a fitness tracking database as an example. You'll learn how to calculate body fat percentage in a query, set up validation rules to prevent invalid entries like divide-by-zero errors, and enforce relationships between values at the table level. We'll also cover important considerations like why some common functions, such as NZ, don't work in every context within Access. The database design techniques shown here are useful for any Access application, not just fitness tracking. This is part 67.
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, calculated fields, table-level validation rules, enforce good data, body fat percentage, query design, round function, null value handling, divide by zero, validation rule property, field-level validation, fat mass less than total weight, NZ function, BMI criticism, BMR tracking
Subscribe to Fitness 67
Get notifications when this page is updated
Intro
In this video, we'll talk about calculated fields, table-level validation rules, and creating reliable data entry in Microsoft Access, all demonstrated with a fitness tracking database as an example. You'll learn how to calculate body fat percentage in a query, set up validation rules to prevent invalid entries like divide-by-zero errors, and enforce relationships between values at the table level. We'll also cover important considerations like why some common functions, such as NZ, don't work in every context within Access. The database design techniques shown here are useful for any Access application, not just fitness tracking. This is part 67.
Transcript
Did you know there's a Microsoft Access function that works in queries, forms, reports, and VBA, but mysteriously fails right where you actually need it?
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today we're going to talk about that, plus calculated fields, table-level validation rules, and how to enforce good data without writing defensive code everywhere.
Just to be clear, you don't have to care about fitness at all for this video. Fitness is just the backdrop that I'm using. The real value here is the database design techniques I'm showing you, and these apply to any Access database, whether you're tracking customers, inventory, orders, or anything else.
Alright, let's get to it.
In the last video, we put together our body composition table. Now in today's video, we're going to start off by calculating our body fat percentage. So, we're going to create a query, query design. We're going to bring that table over here. And since we're going to use this in a lot of different places, I'm going to bring in the star. Right here, we're going to calculate our body fat percentage.
Let me close this. We don't need this guy. I'm going to zoom in so you can see it better. Shift F2. There we go. And we're going to call this guy, let's call it body fat percentage PCT. That's going to be equal to, and in a query use a colon, it's going to be the fat mass divided by the total weight times 100.
Do we need the parentheses? Not really. Order of operations: division and multiplication are at the same level, left to right. That doesn't really matter here. I just find it makes it more readable.
Now, let's hit OK. Let's save this as my body comp q. And let's take a look at what we got here. I'm going to bring body fat percentage over this way, because right next to total weight and fat mass, I'm going to put it right there.
Now, a couple of things to come up. First of all, should you round this number here with a round function? I don't usually. I tend to round stuff only when necessary. I generally like my queries to return raw, accurate data. This isn't presentation-ready. Rounding is a display decision, not usually a data decision.
There are exceptions. There are cases where rounding absolutely belongs here in this step, like if you're calculating sales tax or line items. You've got to round each step, each line on an order, so the pennies add up correctly. If you're doing sales tax, for example, you don't end up with the Superman III Office Space problem that I've talked about in a lot of my videos, where all the fractions of a penny don't get calculated properly. In that case, you definitely would round this number with a round function. But here, this value really isn't being used to calculate anything else, and we're not going to display the query to the end user. The end user is not getting this query. They're going to get a form or a report that pulls this number, and that's where we do the rounding. I don't even use the round function. I just display the number of decimal points that I want.
This isn't a spot where I would add rounding.
Another thing, notice we got a null value there, and that's fine. You might be thinking that we have to take nulls into consideration in here. Let's say, if this is null or this is null, then we need to return a null. But no, you don't have to worry about that, because if any of these values in this calculation are null, the final result is always going to be null, and that's what we want. Null plus anything is null. Null times anything is null. Null to whatever power is null. Null as part of any calculation makes the whole result turn out to be null.
What you do have to worry about is zeros. You don't want a divide-by-zero problem here. You could work that into the calculation or, better yet, let's use a validation rule so they can't enter zero for either of those things. Zero is an invalid value, definitely for these. Fat mass and total weight are never zero percent fat. I believe you would be dead if you were zero percent body fat. Men need to be at least like two percent body fat and for women it's higher, like eight or ten percent.
So we're going to disallow zero values, and we're going to do that at the table level. If we don't allow zeros, we don't have to worry about divide-by-zero errors.
Let's go into the table, design view. Let's go to our total weight and set a validation rule. Really easy: greater than zero. You could realistically put anything you want in there. What's the minimum weight of an adult human being? You could let kids use the database, I guess. You could put like 20 or 30 or 50 pounds in there, whatever you want. Weight cannot be zero or less.
I'll copy this. We'll do the same thing for fat mass. Validation rule, greater than zero. We'll put in here, fat mass cannot be zero or less. We'll do the same thing for lean mass, I guess. You need to have some kind of lean mass in your body, greater than zero. Lean mass cannot be zero or less.
What we're basically saying here is these three values, we're not requiring them. Don't make them required. You don't have to enter them. They can be null, but you can't enter zero. If you put something in there, it's got to be at least one or higher. Or, since these are doubles, technically 0.0000001 or higher. You could make that greater than one or put a realistic number in here. Whatever works for you. But people will accidentally fat finger minus 15; I've had that happen.
Here's one more thing to take into consideration. What if they type in a fat mass that's greater than their total weight? That could happen too. Or lean mass, but we're just going to focus on fat for now. We could do lean another time maybe.
We could set up a table-level validation rule that looks at the relationship between fat mass and total weight and make sure that this isn't greater than that. This is a table-level validation rule. Here's a video I did on this about four years ago. I'll put a link to it down below. It essentially lets you set up a validation rule that looks at multiple fields in the table.
Go to the table's property sheet, make sure you're on table properties and come right here for validation rule. I'm just going to zoom in. For this, you're going to say the fat mass has to be less than the total weight. Hit OK. Be very careful. Look what it just did. This happens in property sheets and it happens in conditional formatting a lot. Access is trying to be helpful and puts quotes around this thinking it's text, but it's not. These are fields. Sometimes you even have to put these brackets in odd places, even though you're not putting spaces in your field names. I hate that; I can't stand that. Watch that. That happens in conditional formatting a lot too.
Let's save it. Do you want to check the data? I'm going to say no. Usually you would have it run through and check and see if your table data is good.
Let's go back over to here. Now, if I put a fat mass in of 20, we're good. If I put 400 in here, it yells. We didn't give it a validation text, but see, we're not allowed to do that. But you can have a null value when they're in, it's just fine, because remember, is null less than anything else? It's null.
Put a validation rule in here. Let's say that fat mass needs to be less than total weight. You can do the same thing with lean mass as well, just have an or condition in here.
Be careful in here, by the way, because if you do want to check for null values, if you do something like this: NZ, comma zero, let's say you want to check and see if each one of these are null. Watch what happens. Save it. Say no to check it. And look, unknown function, NZ, in validation expression. What's that all about?
This is one thing that I can't stand. NZ, it's one of the most popular functions. I use it all the time. It works in queries. It works in forms. It works in reports. It works in your VBA code. I think it even works in macros, but it doesn't work in validation rules. Access team, what's up with that? Why doesn't NZ work? You can use ISNULL. You can use IF, the immediate function, so you can put those two together and get an NZ. But I just can't believe that NZ doesn't work. A basic function like that does not work.
We don't need it for this one. But just keep that in mind for the future. If you ever want to use NZ in a validation rule, you're not crazy. I'll put that one square on the Access team.
That's a simple table-level validation rule. That's just to prevent someone from putting in a higher fat mass than their total weight. That's all.
One thing I also want to add, I know I'm going to get comments about BMR. There's also a measurement called BMI, body mass index. This is basal metabolic rate. That's how many calories you burn at rest. BMI, body mass index, is basically a nonsense number that the government came up with. It's basically a ratio. It's a chart they put together. It's basically your height versus your weight, and it's a BS number. It tries to label people as underweight, normal, overweight, or obese based on that chart.
Here's the chart on the government's website. Now, I'm 5'11". According to this, my healthy range is 136 to 172. No. A while back, I got down to 185. I looked like I had cancer. I do not look healthy at 185, let alone 172, which is the higher end of their healthy weight.
You could be a 200-pound weight lifter at 5'11 and be ripped with muscle, and you'd have the same BMI as someone who's basically very overweight. Which one is healthy? So BMI, don't pay any attention to the BMI table. The only time BMI is remotely useful is as a rough population statistic, and if you absolutely can't afford a smart scale that'll tell you what your BMR is, I guess you can kind of use this, but no, I don't refer to it.
For personal fitness tracking, you want body fat percentage if you can get it, and the actual composition data for your BMR.
So our table's pretty much done. I got all the fields there I want to track. We got our query setup. We got our body fat percentage. Now we're all set to start working on our data entry form, and we'll make a little chart form. We got all kinds of cool stuff coming up.
So the big takeaways from today are this:
Keep your calculations in your queries.
Keep your presentation logic in your forms and your reports.
Use table-level validation rules to prevent bad data from ever getting into your database in the first place.
Remember, not every function works everywhere in Microsoft Access, and sometimes you have to adjust your approach depending on where that logic lives.
Again, I'll repeat it: This may be a fitness database, but the techniques you saw today apply to any Access project, whether you're working with customers, inventory, orders, or anything else.
Let me know what you thought of today's video in the comments below, and if you've ever been bitten by a function that mysteriously didn't work where you needed it to, I want to know about it.
That's going to be your TechHelp video for today, brought to you by AccessLearningZone.com.
Live long and prosper, my friends. I'll see you next time.
Quiz
Q1. Where does the presenter recommend placing calculations like body fat percentage?
A. In table fields
B. In queries
C. In forms only
D. In reports only
Q2. Why does the presenter NOT usually round numbers in queries?
A. He prefers to save processing time
B. Rounding is a display decision, not a data decision
C. Queries do not support rounding
D. Rounded numbers are more accurate
Q3. In which situation does the presenter recommend rounding numbers directly in the query?
A. When displaying general statistics
B. When calculating sales tax or order line items
C. When storing raw data
D. When creating charts
Q4. What is the issue with having a zero value for fat mass or total weight?
A. It causes a null error
B. It results in negative body fat percentage
C. It can cause a divide-by-zero error
D. It displays incorrect chart data
Q5. How does the presenter enforce that fat mass and total weight cannot be zero or less?
A. By writing VBA code for every form
B. By using a query-level restriction
C. By using table-level validation rules
D. By hiding those fields from users
Q6. What is a table-level validation rule designed to do in this context?
A. Prevent duplicate records
B. Enforce relationships between fields like fat mass and total weight
C. Limit decimal places for calculations
D. Ensure faster performance
Q7. What happens if you try to use the Access NZ function in a table-level validation rule?
A. It works as expected
B. It gives a syntax highlighting
C. It results in an error saying "unknown function, NZ, in validation expression"
D. It prompts the user for input
Q8. What does the presenter state about handling null values in calculations?
A. Null values return zeros
B. Null values are automatically handled in calculations and result in null
C. Null values cause Access to crash
D. Null values must be manually converted to blank strings
Q9. What is the main reason the presenter dislikes the BMI (Body Mass Index) chart for individual fitness tracking?
A. It is difficult to calculate
B. It does not account for muscle vs fat composition
C. It requires using a government website
D. Its calculation is too complex for Access
Q10. What are the key takeaways from the video regarding good database design in Access?
A. Keep presentation logic in tables, use code for validation only
B. Do calculations in forms, and never use table validation
C. Keep calculations in queries, presentation logic in forms/reports, and use table-level validation to prevent bad data
D. Always use VBA macros for every database operation
Answers: 1-B; 2-B; 3-B; 4-C; 5-C; 6-B; 7-C; 8-B; 9-B; 10-C
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 a frustrating quirk in Microsoft Access: a function that works flawlessly in queries, forms, reports, and VBA, but fails exactly where you might expect to use it most. In this lesson, I also cover calculated fields, table-level validation rules, and how to ensure strong data quality without scattering defensive code everywhere in your database.
Although I'll use fitness tracking for my examples today, the core database design techniques I demonstrate work with any subject matter. Whether your Access database is for customers, inventory, orders, or something else entirely, these strategies apply across the board. Fitness is just our working example.
Let's start with the foundational setup from my previous lesson, where I assembled a body composition table. Today I move into calculating a body fat percentage. To do that, I design a query based on our body composition table. I use a calculated field within the query to figure out the body fat percentage: simply fat mass divided by total weight, multiplied by 100. For clarity, I use parentheses, although the order of operations (division and multiplication) makes them technically unnecessary here. Adding them just improves readability.
Generally, I do not round calculated values like body fat percentage at this stage. I believe in keeping the data as precise as possible in queries, saving any rounding for when the data is displayed to an end user in a form or report. There are situations where rounding at the query level is vital, such as calculating sales tax or line item totals, to guard against cumulative floating-point errors — the infamous "Superman III/Office Space" penny-skimming bug. In those cases, each value must be rounded at every step. However, for our current purpose, since these figures are mainly for summary or display and not being used in further calculations, rounding at this point is unnecessary.
A common concern when calculating values is handling nulls. In Access, if any part of the calculation involves a null, the result will also be null. That's what we want — there is no need for extra logic to check for nulls in this calculation. However, zeros can be an issue, especially for division. You don't want a divide-by-zero error. It's better to put a validation rule in place at the table level to prevent users from entering zero for the total weight or fat mass fields. These fields never realistically have a value of zero for a living human being, so it makes sense to forbid it.
Within table design view, I add validation rules to ensure total weight, fat mass, and lean mass values are all greater than zero. I also include helpful validation text to explain the rule to users. These fields are not set as required; they can be left blank (null), but if a value is entered, it must be above zero. Realistically, you may want to tailor the minimum allowed value for your own requirements, but this approach stops both input mistakes and impossible values.
Another point to consider is preventing illogical combinations, such as entering a fat mass value greater than the total weight. This is where table-level validation rules shine. You can define a validation rule at the table level that makes sure, for instance, fat mass can never exceed total weight. To do this, you use the table's property sheet and establish the rule. Be careful though: Access sometimes tries to "help" by adding unnecessary quotes or brackets, especially when fields are named without spaces. Always double-check how Access has formatted your field names and rules.
After setting up this validation, attempts to enter a fat mass higher than the total weight are blocked, displaying an error. However, nulls are allowed, since a comparison between a value and null results in null, which is acceptable.
If you want to allow similar validation for lean mass, the logic is identical — just extend the rule accordingly.
As a side note, you might wonder if you can use the NZ function in a validation rule to substitute nulls with zero. Unfortunately, one of Access's oddities is that the NZ function, despite being widely used in queries, forms, reports, and even VBA, does not work in table-level validation rules. If you try, Access will provide an "unknown function" error. For validation rules, rely on other functions that Access supports, like ISNULL or the Immediate IF (IIF) function. This limitation is an ongoing frustration for many developers.
To wrap up the modeling discussion, let me briefly speak about related concepts like BMR and BMI. BMR stands for Basal Metabolic Rate--the calories burned at rest. BMI, or Body Mass Index, is a government-endorsed measurement that purely considers height and weight and attempts to categorize people as underweight, normal, overweight, or obese. However, BMI ignores body composition, so a muscular weight lifter and someone overweight of the same height might get the same BMI. For personal fitness, BMI is only somewhat useful as a rough population statistic, or if no better measurements are available. For tracking actual health, body fat percentage and other composition metrics are more relevant.
In summary, today we discussed essential Access techniques:
- Keep your calculations in queries
- Reserve presentation logic for forms and reports
- Employ table-level validation rules to prevent bad data at the source
One key lesson is that not every function works in every context within Access, so design your solutions with your context in mind.
Although these examples center on fitness, all of the techniques I demonstrated are applicable to any Access project, from customer management to inventory or order tracking.
You can watch the complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Calculating body fat percentage in a query
Creating calculated fields in Access queries
Deciding when to apply rounding in queries
Handling null values in Access calculations
Preventing divide by zero errors with validation rules
Setting field-level validation rules for numeric input
Applying table-level validation rules based on multiple fields
Ensuring fat mass is less than total weight with validation
Understanding why the NZ function fails in validation rules
Alternatives to NZ for validation expressions
Allowing null but disallowing zero or negative inputs
Comparing field values for logical validation
Separating calculation logic from presentation logic
Applying database design techniques for robust data entry
Article
Did you know there is a Microsoft Access function that works in queries, forms, reports, and VBA, but fails right where you might expect to need it most? This article will guide you through important database design concepts in Access, focusing on calculated fields, table-level validation rules, and best practices for ensuring good data without endless defensive coding. While the examples use fitness tracking as the context, these principles apply to any database functionality you might be building, whether your data involves customers, inventory, orders, or anything else.
Let us start by calculating a body fat percentage in our database. Suppose you have a table where you are storing details like total weight, fat mass, and lean mass. You want to create a query that calculates the body fat percentage. You can do this easily in Access. Open your query in design view and bring in your table. To calculate the body fat percentage as a new field in your query, use an expression like this:
BodyFatPCT: [FatMass] / [TotalWeight] * 100
The colon after the field name (BodyFatPCT) is how you define an alias in Access queries. You do not necessarily need to add parentheses unless you want to clarify the order of operations, since division and multiplication happen left to right in Access. Once you have saved and run your query, you will see the calculated body fat percentage alongside your other data.
Now comes an important decision: should you round this number at the query stage? In most cases, you should not. The purpose of a query is typically to return accurate, raw data suitable for any downstream calculations or reporting. Rounding is a presentation concern, so handle it when you are showing data on forms or reports by setting the number of decimal places for display. There are exceptions, such as when you calculate line item totals or sales tax, and you want to prevent rounding errors adding up across multiple records. In those cases, you definitely want to round at the query level. But for calculated fields like body fat percentage that are not used as the basis for further calculations, rounding at the display stage is sufficient.
A related issue is how to manage null values in your calculations. You might wonder if you need to account for nulls in your formula, but Access handles this for you. If any value involved in the calculation is null, the whole result becomes null automatically. That is usually what you want, since an unknown or missing number means your final result is also unknown.
You need to be careful, though, about dividing by zero. If total weight is zero, your calculation will fail with a divide-by-zero error. The best way to prevent this is to set up validation rules at the table level to stop users from entering invalid data in the first place. For example, total weight, fat mass, and lean mass should all be greater than zero. To enforce this, open your table in design view and set the Validation Rule for each of these fields to:
>0
Set the Validation Text to something like "Value must be greater than zero" to give clear feedback to your users. By controlling this at the table level, you prevent bad data from slipping in, and you no longer need to constantly handle these issues in your queries, forms, and VBA code.
These fields do not have to be required. Users may leave them blank (null), but if a value is entered, it must be above zero. This is important both for realistic data entry and for database integrity. If you want to enforce a realistic minimum (for example, 30 pounds for weight), you can change the Validation Rule accordingly.
Another smart validation is to ensure the relationship between multiple fields remains consistent. For instance, fat mass should never be greater than total weight. To enforce this, you need a table-level validation rule, which looks at multiple fields together. To add a table-level validation rule, open the table's property sheet, and set the Validation Rule to:
[FatMass] < [TotalWeight]
Be careful in this property sheet, as Access sometimes tries to auto-format your input by adding quotes around field names. You want to reference the fields directly--not as literal strings--so remove any extra quotation marks that Access adds.
This table-level validation blocks any attempt to save a record with invalid relationships between the columns. If a user tries to enter a fat mass higher than the total weight, Access will prevent them from saving the record. You can add a custom Validation Text message to explain the rule to the user.
Suppose you want the validation rule to allow for nulls as well, but sometimes you might be tempted to use the NZ function to treat null values as zero. However, this is where you will hit an Access quirk. The NZ function, which replaces nulls with another value, works in queries, forms, reports, and VBA. But it does not work in validation rules at the table level. If you try to use NZ in a table validation rule, Access will say "unknown function." As a workaround, you can use ISNULL or the IIF function to check for nulls, though for many simple validation cases you do not need to, since comparisons with null return null (which is treated as "pass" for validation).
With these rules in place, your database will block invalid values such as zero or negative weights, and it will prevent logical errors like fat mass exceeding total weight--all without you having to constantly check for these issues in every query, form, or VBA routine.
As an aside, the article mentioned BMI (Body Mass Index) and BMR (Basal Metabolic Rate). While BMI is a widely used metric based on weight and height, it has significant limitations. It cannot distinguish between body fat and muscle mass, so highly muscular individuals may register as overweight or obese by BMI even if they are in excellent health. For tracking meaningful fitness metrics, calculating body fat percentage and detailed body composition is far more informative. You can choose to include BMI calculations in your database if you wish, but understand its limitations.
In summary, the best practices to follow for solid Access database design are: keep calculations like body fat percentage in queries, handle rounding and data presentation in your forms and reports, and guard against bad data by setting up validation rules at the table level. Remember that not every function works everywhere within Access, so test your expressions and be aware of these limitations as you design your database.
Whether you are building a fitness tracker or any other kind of business application, these database design techniques will help keep your data accurate and your application logic simple. By putting validation where it belongs--at the data layer--you free yourself from constantly writing and rewriting defensive code throughout your application. With a little planning and an understanding of how Access handles calculations and validation, you can ensure your database stays healthy whether you are tracking weights or widgets.
|
