Fitness 50
By Richard Rost
28 days ago
Auto Calculate Totals for Checked Fitness Items In this Microsoft Access tutorial, I will show you how to calculate totals for only the checked items in a continuous form. We'll work with a fitness database tracking calories and protein, add checkboxes to mark items as eaten, and update a query to total just those checked entries. I'll demonstrate how to set up calculated fields in the query, display totals on the form, and use an event to refresh totals immediately when records are updated. We'll also talk about selecting a range of records using shift-click, which will be covered in detail in a future lesson. This is part 50. 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, calculate totals checked items continuous form, sum checked checkboxes, food log query, total calories eaten, total protein eaten, has eaten field, after update event, me.dirty false, select all function, shift click select records, fitness database tracking, query design view
Subscribe to Fitness 50
Get notifications when this page is updated
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today I'm going to show you how to calculate totals just for the checked items in a continuous form in Microsoft Access.
You have a column of numbers, a particular field. You can total it up on the bottom. We know how to do that. That's easy with just a sum function. Now we're going to have checkboxes over here, and we're going to total up just the checked items.
We're doing this in our fitness database, but it doesn't matter if you care about track and fitness and calories. We're doing calories and protein here, and I just want to see how many I've actually eaten so far. Of all the stuff in this column, I've only actually eaten 500. But this works with any kind of database you want.
Here we go. First of all, welcome to part 50. I never thought I'd get up to 50 in this, but I'm having fun with it. We're learning some stuff. I've been throwing in a lot more cool tricks because the more I use the database, the more things I'm like, well, I want it to do this for me. You are just benefiting from the cool stuff I want to do with this.
The first thing I want to do is that select all function that we built for this guy. Remember, select all just when you click on a cell, it selects all the text. I'm just setting it for all of these guys here. Right-click properties, and then I put it in the event right here equals select all cell all. I find it helpful that when I click on one of these guys, I want to have it highlight that. And for the members, I want to be able to just as easily hit equal and it pops that up.
I also forgot to put it in this box here. We put it in these; we didn't put it in this one. So right-click, design this guy, and this will be also so.
Next up, sometimes I want to just quickly at a glance see what a particular meal was as far as calories or whatever. It would be nice if I could click on this guy and then click on these other ones here and maybe see down here a total - total eaten, total calories eaten, total protein. Sometimes I like to plan the day out, plan the whole day out, but I haven't eaten this stuff yet. So I want to see where exactly am I at. That's a cool trick we can also put into this.
We've got the totals down here. Then we'll have the total eaten, and then eventually we're going to put our goal in with our goal calories and goal protein, whatever other goals you have. We're going to do that soon because I like to put that in there too. Then we can also see how many you actually eat.
We might, I haven't decided yet, do a trick where we can click on the top box and then hold the shift key down and click on the bottom box, and it'll select everything in the middle. I haven't decided; we might put that in. I'm just thinking about these things at this point.
Let's do the first thing first here. This form is based on my food log Q. What we're going to do is, if you want to have totals down here, then the values that you're totaling should be in the query itself. That's easy to do. We've already got total calories, total protein. Now we just, with a little if function, can tell if those have been eaten or not.
So let's go to the query. Design view. We've got total calories and total protein. I'm going to just zoom in here, Shift-F2, make it so I can see the other stuff. There we go.
Total calories eaten: we'll use an if function. If, and I believe the field is called eaten. Let's see. Yep. Oh, has eaten. There, see it? Has eaten. If has eaten, now we can just leave it at that because has eaten is a true/false value. We don't have to say equals true; we can just leave it has eaten. That's a shortcut. So if has eaten, then calories or total calories eaten is going to be equal to total calories. Otherwise, zero. That simple.
We'll do the same thing with protein. Let me copy this guy. We'll do this over here. Zoom in. Paste it. This will be total protein. And whatever other fields you are tracking for yourself.
To run it, we take a look. Total protein, total protein, total calories. Now, let me check at the bottom here for something I checked already. Here are some. I don't usually use these check boxes myself because they don't do anything right now, but we're going to make them do some stuff. So I'll start using it in the future. You can see here, if it's checked, it shows up; if not, it's a zero.
Now all we have to do is come into the log and add some stuff on the bottom down here. Let's make this a little bit smaller. I barely use these food log notes, by the way. I'm going to copy and paste this guy and put it here. We'll call this guy total and we'll make it right aligned like that.
Then I'm going to just copy all three of these, copy, paste. It doesn't work. Of course. Paste, copy, paste. Come on, copy, paste. This bug is really annoying me. It works if you do them individually.
Slide these up. Total eaten. Now this guy will be sum. Sometimes you have to have the value up here; sometimes you don't. I'm going to guess that we don't in this case. Let's find out. Here, sum total calories eaten. Usually you don't, and I like to call it the same thing. Sum total calories eaten.
Let me figure it out. My database is much, much bigger on my screen than you guys can see. When I'm working by myself, I make it giant.
Sum total protein, and this will be sum total protein. Sum total protein.
Save it. Let me get a slightly different color. Let's go with - what do you want? Let's do like all I have. There we go.
Save it. Close it. Open it. Let's check some stuff. Let's do lunch. Notice it's working, but it doesn't work until you leave the record. Now I have to click up here because that record's dirty. All we need is a little event in here that saves the record immediately. In my eaten check box or in the quantity check box - these things will both affect this - we'll do it in both of them. We'll do it in an event, in the after update event.
All you really have to say is Me.Dirty = False. That will just save the record immediately. Come back and do it for quantity as well - after update, same thing.
Now close it. Close it. Open it. If we check stuff - check. Check. Immediately. Check. Check. So I know that meal was 775 calories. I made protein pasta today. It wasn't bad. It takes a little getting used to the taste, but it's much, much better for you than regular pasta, which I love. I'm half Italian; I love pasta, but I'm trying to find foods that are higher in protein and lower in carbs. I made the box and I shared it with my wife, and she liked it.
Now, what about the other trick? What about the trick where I can click on this guy, then hold the shift key down and click on that guy, and it selects everybody in the middle? We can get away with that because all of our records are time sorted, and none of them can have the exact same time because of what we did earlier with that one-second trick.
Otherwise, you'd have to have some way of sorting these, or whatever. We can definitely do that with a little bit of code, and we'll talk about that in tomorrow's class.
Tune in tomorrow, same bat time, same bat channel. Or if you're a member, you can watch it right now because I recorded a bunch of these today. Sometimes I go for two or three days without recording anything, and then I just get in the mood to record a bunch of stuff at once. I'm going to probably do a bunch of these today.
This video is going live Wednesday, the 15th of October, 2025. So tomorrow, Thursday, will be another fitness video. Then we've got Quick Queries Friday, and then we'll continue on Monday, the 20th.
There we go.
Oh, wait, don't go yet. I just realized these guys aren't visible for some reason over here. None of those labels are showing up. I think it must be a leftover from when, for some reason, this guy wasn't visible by default.
Let's take all these guys and go Visible: Yes. Save it.
I was putting the slides together for this, and I was thinking, why don't I see the totals over here? It's working now.
Bye-bye.
That is going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Calculating totals for checked items in a continuous form
Creating calculated fields in a query to sum checked items
Using an IIf function in a query for conditional totals
Displaying total calories eaten based on checkbox status
Displaying total protein eaten based on checkbox status
Adding calculated total controls to a form footer
Configuring form controls to display conditional totals
Forcing form records to save after updating checkboxes
Using the After Update event to trigger record save
Ensuring totals update immediately upon data entry
Making form labels and controls visible as needed
COMMERCIAL:
In today's video, we're learning about how to total just the checked items in a continuous form in Microsoft Access. I'll show you how to set up your query to track things like calories and protein, but this will work in any kind of database where you need to sum up only the items with a checked box. We'll discuss using the IIf function to make this happen, how to adjust your forms to display these new totals, and how to use the AfterUpdate event to save records instantly so your totals update right away. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the main goal demonstrated in the video tutorial?
A. Summing all records in a continuous form in Access
B. Calculating totals for only the checked items in a continuous form
C. Filtering records by date in Microsoft Access
D. Linking Access forms to Excel spreadsheets
Q2. In the context of the video, what does the checkbox field "has eaten" represent?
A. Whether the user has created a new record
B. Whether a meal or item has been marked as consumed
C. If the total calories are above the set goal
D. If the record has attached notes
Q3. Why should the fields to be totaled appear in the query underlying the form?
A. So that the data appears in alphabetical order
B. So that only calculated controls will use them
C. So that they can be easily summed in the form footer
D. To enable sorting by ordinal values
Q4. How is the calculated field for "total calories eaten" determined in the query?
A. By dividing total calories by the number of items
B. Using an If function that checks if "has eaten" is true
C. By simply multiplying all calories
D. By averaging only the checked items
Q5. What Access function is used in the calculated fields to determine totals based on the checkbox?
A. Count
B. DateDiff
C. If (IIf)
D. DLookup
Q6. What does the expression "Me.Dirty = False" accomplish in the event procedure?
A. Cancels any pending user changes
B. Sorts records by the "dirty" field
C. Automatically saves the current record
D. Reverts the form to its original state
Q7. Why is it important to update the record immediately after checking the "eaten" checkbox or changing the quantity?
A. To refresh the entire database
B. To ensure the calculated totals are updated without changing records
C. To move to a new record automatically
D. To backup the database right away
Q8. What visual indicator is added to the continuous form to show the sum of checked items?
A. A highlighted row
B. Additional checkboxes at the top
C. Totals in the form footer based on the calculated "eaten" fields
D. A message box pop-up
Q9. If a user wants to check multiple items at once (e.g., select a range by holding shift), what factor allows for this implementation according to the video?
A. Each record having a unique time value for sorting
B. All records being checked by default
C. The sum function automatically selecting ranges
D. The Access form being read-only
Q10. Which event is used in the checkbox and quantity controls to trigger saving the record in the video solution?
A. OnLoad
B. AfterUpdate
C. OnClick
D. OnOpen
Q11. What is suggested should be set to "Visible: Yes" if totals or labels are not appearing on the form?
A. The query default view
B. The font color of the form
C. The visibility property of the control or label
D. The Default Value property of the label
Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-C; 7-B; 8-C; 9-A; 10-B; 11-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 TechHelp tutorial from Access Learning Zone covers how to calculate totals for only the checked items in a continuous form in Microsoft Access.
Imagine you have a database with a column of numbers, representing something like calories or protein in a food log. Normally, you can add up all the values in this column using the Sum function, and display that total at the bottom of the form. However, you might want to calculate the total only for items that are checked, indicating, for example, foods you have actually eaten. This approach is relevant for any kind of database where you want to tally up selected items, not just for a fitness or food log.
In my example, I'm using a fitness database to track calories and protein. Sometimes I plan all my meals for the day, but I want to see how much I've actually eaten versus what is still planned. Only the foods I've marked as "eaten" should contribute to the running total. This method can be used for any setup where you want selective summing based on a condition.
This lesson follows my usual approach - as I add more features to my database to make it more useful for myself, I share those new tricks with you. I hope you find them just as helpful in your own projects.
To get started, let me quickly mention a useful usability trick. I like to set up a "select all" function for my form controls. When you click inside a text box, all the text gets highlighted, making data entry faster. You can set this up in the properties for each text box, using an event procedure that triggers the select all action, making editing smoother throughout your form.
Now, focusing on the totals for checked items: suppose you want to see at a glance how many calories or grams of protein you have actually consumed (not just planned). It is helpful to be able to click on different records, use checkboxes to indicate which items are "eaten," and then see the real-time totals for those checked entries at the bottom of the form.
If you want accurate totals in your form footer, the fields you want to sum need to be available directly in the underlying query. In my food log query, I already have fields like total calories and total protein. Now, I create additional calculated fields: total calories eaten and total protein eaten. To do this, I use an If function in the query design. If the "HasEaten" checkbox is true for a record, the query returns the value from the corresponding calories or protein field; otherwise, it returns zero. Since "HasEaten" is a Yes/No (true/false) value, you can plug it directly into the expression to simplify things.
Copy and adjust the formula for each nutritional value you want to track. For example, you might have total protein, total carbs, etc., and the query will output the value if the item is checked, or zero if not.
When you run the query, you will see these new calculated columns. Now, back in the form design, add new unbound text boxes to the footer area. Bind them to expressions that sum your new calculated fields, such as the sum of total calories eaten or total protein eaten. Adjust formatting and alignment as needed so the totals appear clearly labeled in your form.
You may run into a small nuisance here: when you check or uncheck a box for an item, the totals do not update instantly until you leave the current record. To fix this, add a simple event to the after update event of your checkbox and other relevant controls, such as quantity. All you need to do here is force the record to save immediately by marking it as clean. Once you add this event, the totals will update instantly as you check or uncheck items.
Everything should now work smoothly. As a side note, I tried protein pasta for lunch, and while it's different from traditional pasta, it offers better nutrition for those focusing on high protein and lower carbs.
Looking ahead, I have considered adding a convenient trick where holding the shift key and clicking between two checkboxes selects everything in between, making batch updates easier. This is possible because the records are time-stamped individually, thanks to previous design decisions. I will be covering this technique in a future lesson.
If you notice labels or controls are not visible where they should be, double-check their visibility settings in design mode and set them to visible if necessary.
That covers today's lesson on summing checked items in a continuous form in Microsoft Access. If you want a complete, step-by-step video walk-through of everything discussed here, you can find it on my website at the link below.
Live long and prosper, my friends.
Topic List
Calculating totals for checked items in a continuous form
Creating calculated fields in a query to sum checked items
Using an IIf function in a query for conditional totals
Displaying total calories eaten based on checkbox status
Displaying total protein eaten based on checkbox status
Adding calculated total controls to a form footer
Configuring form controls to display conditional totals
Forcing form records to save after updating checkboxes
Using the After Update event to trigger record save
Ensuring totals update immediately upon data entry
Making form labels and controls visible as needed
Article
In this tutorial, I will teach you how to create a running total in a Microsoft Access continuous form, but only for records where a checkbox field is checked. This is a great way to monitor specific subsets of your data, like items that are completed, meals that have been consumed, or tasks that have been marked as done. I will use a fitness and food log example, but you can adapt the technique to any database scenario where you want to total only selected items using a checkbox.
Let's start with the principle. Suppose you have a table or a query tracking individual entries, such as meals, with fields like TotalCalories, TotalProtein, and a yes/no field (checkbox) called HasEaten. Normally, using the SUM function in a form footer will total up all the values in a column. But what if you only want to sum up those where the corresponding checkbox (HasEaten) is checked? For that, we need to use a conditional calculation in the query that drives the form.
Begin by opening your query in design view. You should already have fields such as TotalCalories and TotalProtein in your query. Add a new calculated field for each total that you want to filter. For example, to sum calories only for entries where the meal has been eaten (HasEaten is checked), create a field like this:
TotalCaloriesEaten: IIf([HasEaten], [TotalCalories], 0)
This formula uses the IIf function to check if the HasEaten field is true for that record. If so, it returns the value of TotalCalories; otherwise, it returns zero. This way, for records not marked as eaten, you are adding zero to your sum, which effectively excludes them.
Do the same for other fields you wish to track. For instance, to tally protein:
TotalProteinEaten: IIf([HasEaten], [TotalProtein], 0)
After adding these calculated fields, save your query.
Next, open your form that displays this data. In the form footer (or wherever you want the subtotal to appear), you can use the SUM aggregate function to display the totals of only those records that meet your criteria. Place a textbox control where you want to see the total and set its Control Source to:
=Sum([TotalCaloriesEaten])
This will sum up all the values in the TotalCaloriesEaten column, which, as we set up, already excludes the unwanted records. Do the same for protein, using =Sum([TotalProteinEaten]).
Now, let's make the totals update right away as you check or uncheck the HasEaten box in the form. By default, Access does not recalculate the footer sum until you move off the current record. To correct this, you want to force Access to save the record as soon as you make a change, ensuring the sums update instantly. To do this, add a little VBA in the AfterUpdate event of any checkbox or field that should trigger the update.
Open the form in design view, select the HasEaten checkbox, and open its property sheet. In the After Update event, add this code:
Me.Dirty = False
This command tells Access to save (commit) the current record, which immediately triggers recalculation of your sums. If you have other fields, like quantity, that should also cause the totals to refresh when changed, add the same event to their After Update properties.
Now, when you check or uncheck HasEaten in your form, the totals in the footer update immediately.
Make sure your footer text boxes are visible and properly labeled, so users know what the totals represent. If your labels or text boxes are not showing, select them in design view and check that their Visible property is set to Yes.
You can take this concept further and add more user-friendly features, such as a way to highlight or select ranges of records, but the above steps cover the core functionality of totaling up just the checked records in a continuous form.
This approach helps you interactively track your progress or analyze subsets of your data, whether you are counting finished tasks, consumed meals, or any other scenario with a yes/no filter.
If you wish to expand on this, for example, letting users select a range of records with shift-click to bulk check or uncheck, you will need to add more complex VBA. But even just using the conditional sum approach described here will significantly enhance your form's usability.
In summary, by adding conditional calculated fields to your query and summing them in the form footer, combined with a bit of VBA to save records upon update, you can create dynamic, real-time rolling totals based only on checked items in Access forms.
|
