Attendance 4
By Richard Rost
5 months ago
Tracking Attendance for Students and Employees Part 4 In this Microsoft Access tutorial, I will show you how to add group and report totals to your attendance report, including calculating total count, present count, and absent count using text boxes and functions like Count and Sum. You will learn how to set up these totals in both the group footer and the report footer, format your report for clarity, and add a button to open the report in Print Preview. Basic troubleshooting tips for copying controls and understanding Yes/No fields are also covered. This is part 4. 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, attendance report, report totals, design view text boxes, count function, sum function, present count, absent count, report footer calculations, customer ID grouping, yes no field values, report sorting, add sorting level, preview report button, printing reports, formatting totals
Subscribe to Attendance 4
Get notifications when this page is updated
Transcript
Today's part four of my series on taking attendance. Go watch parts one through three and then come on back.
All right, our database is starting to get there. Now we have our report pretty much set up. Let's go in here and print preview it. This is what we're working with. Looks good.
Let's put some totals down here. We're going to put those in text boxes. I'm going to go back into Design View. Right in this band here, we're going to put them in the customer ID footer. I'm going to go up to Report Design, grab a text box, and drop it down here. This is going to be the total count.
Change this to say "total count." Let's make this text black so we can actually see it. Let's make this background transparent so we don't see the white background. Right over here, set it to transparent. There we go. Let's bring this a little bit closer, and it doesn't have to be quite so big.
There's our total count. Double click on this. Actually, this has a border around it, doesn't it? That's the problem with bringing stuff in right from the toolbox - it puts borders around it. Set it to transparent.
Open up this text box. This is going to be my total count, and yes, the name is important. The control source - I'm going to zoom in so you can see it, Shift+F2 - is going to be equals. We're going to use the Count function. What are we counting? Everything. We're going to count the number of records that appear in the section above, which in this case is the detail section, and that's how you represent it: Count of star inside of double quotes.
Let's save it and see if it looks good in print preview. There we go. There's our three, and we've got a box to get rid of again. So everybody should have a three. That's fine. That's normal.
Back to design view. Let's get rid of that box. Format, Shape Outline, Transparent.
Now let's copy and paste this text box. Copy, paste. You used to be able to go copy and then paste and it would put it right below. Sometimes it does, sometimes you have to go copy, click paste, and even then it doesn't work. Access team, you have a bug here. I should be able to go copy and paste. Let's work on that. There it goes.
See? I just clicked and highlighted just the text box, copied, then clicked paste. This behavior is not you - you're not crazy. It drives me nuts too sometimes. This copy-paste behavior is all over the place. It's wild.
Now for this one, this is going to be our present count. You can call it present count or just present. That's fine, whatever you want. Slide it down like that.
Actually, let's right-align these fields. Format, Right Align. There we go. It's better.
Now, for present count, we can't just count this because it will give you three every time. There are always going to be three of these, but the value is going to be out of the two refalls. If you know the way that data is stored internally, it helps you understand how to count the checkboxes.
Internally, a Yes/No field - No/False is zero and True, you would think is one, but it's negative one. Why do they do it that way? It's a long story. I'm not going to go into it now. Some other database systems use one as true, like SQL Server uses one. That always throws me because I'm used to Access being negative one.
Now that we know that it's negative one for true, what we can do is add them up using the Sum function. We're going to count up the number of presences there are using Sum. We're going to add them all up and then multiply that result by negative one to flip it.
This is going to be - and then we're going to call this one present count, because you already have a field called present. The control source is going to be equals the sum of present times negative one. Add up all the values of all the present fields and then give me the negative of that.
Save it. Right-click. Print preview. Look at that: two, three. This guy should be one. Yep. Okay.
Looking good so far. Let's bold these. Actually, let's bold the numbers. Where's bold? Format.
We have one more to do. I'm going to move this over here like that. Let's put them side by side. Takes up less space.
Now, absences are next. Absences are easy. We just subtract that from that. So let's copy and paste. See, again - click on the text box, copy, paste. I don't like that. It didn't use to work like that, Access team.
Absent is going to be absent count. I'll zoom in again. This is going to be equals total count minus present count. That's why I said those names are important. I used to not, back in the day, bother naming my boxes unless I was going to refer to them somewhere else, because you don't want to refer to something as text4. That's completely useless. But now I'm in the habit - I name everything. I don't always name labels because labels will tell you what they're attached to, but text boxes I do.
Save it. Right-click. Preview. There you go. Let's bring it a little closer to the number in Design View. This guy, this guy, this guy, we're going to format left. This guy, this guy, we're going to format right. Okay, let's see if it looks better. There we go.
You can rearrange them if you want the total count on the end. I think absent is the most important value.
Want to get the whole thing for the whole class? We have another footer down here. Now you can't put this in the page footer. There are a lot of reasons why you can't do calculations in the page footer. There are some exceptions and I have different videos on that, but for today we're not going to bother with the page footer. We are going to use the report footer.
This will give you the complete total report of everything. Very similar, we can use these boxes. In fact, we just have to rename things slightly. So copy all of this. Let's see if we can copy, click paste. Good. Sometimes it works, sometimes it doesn't. Who knows.
This formula is going to be the same. We're still counting everybody above it. It just happens to be in the report footer, so the stuff above it is the entire report. But we have to rename it so we can calculate stuff properly. This is going to be the report total count. This one is going to be report present count. This one is going to be subtracting those two, so we'll call this one report absent count.
It's going to be equals report total count minus report present count. One of the benefits of not using the zoom box: if you can see it in the little IntelliSense that drops down there, you lose that with the zoom box. Hey, Access team, that would be a great feature to add - put IntelliSense in the zoom box. I use it all the time for teaching.
Save it. Close it. This time we're going to close it. Let's open it back up again, and there's our totals for everybody. Whoops. Click. There's our totals for everything. Wish you could make these borders thicker. I have a hard time grabbing that. Looks good. Total of 18. You could put percentages over here, like what percentage of classes they made. I mean, there's all kinds of stuff you can do.
Now, how do we open up this report from here? We just make another button, Design View. Then we're going to grab a button, click it there. We're going to Report Operations, Preview Report. I always like to open up my reports in Preview Mode because then if I like them I can print them. You can make a button that will directly print it if you're confident it's the same report you generate every day, and you don't want to have to look at it then hit print. If you don't mind wasting paper, you can make a button. I prefer just doing Previews. I hardly ever print stuff. I rarely ever print stuff.
What report do you want? That's our attendance. I'm going to put in here attendance report. Next, give it a meaningful name: Attendance. Our button. Then finish.
There's your button. Save it. Close it. Open it. I'm going to shrink up that ribbon again, and click the button. There it is.
Now, there is a little minor issue. I just noticed that if you look, these are not sorted. So we can add a sorting level in our report.
Let's go back into Design View. Right now we are grouping on customer ID, but we're not sorting on anything really. If you take a look at it, we're going to Print Preview. Richard Rost, James Kirk. These are actually coming in sorted by, I believe, customer ID.
So we have to sort by that, then sort by the date inside. Notice these dates are all scrambled, too. We'll talk about sorting all of this, and I have a couple more tricks for you. We'll talk about that in tomorrow's class.
Tune in tomorrow, same bat time, same bat channel. Members, I'm going to watch it tonight. I'm still working on it. But that is going to do it for your TechHelp video for today. That's part four. Hope you learned something. Live long and prosper, my friends.
I'll see you tomorrow for part five.
TOPICS:
Adding total count text box to report footer
Formatting text box appearance and alignment
Using Count function to total records in a report section
Copying and pasting text boxes in Access reports
Calculating present count using Sum and Yes/No fields
Multiplying Sum results to correct Yes/No totals
Naming text boxes for referencing in calculations
Calculating absent count using total and present counts
Formatting numeric fields as bold and right-aligned
Displaying totals for entire report using report footer
Renaming controls for report-level calculations
Opening report from a form with a command button
Previewing reports using command button wizard
Adding sorting levels and grouping in reports
Sorting report data by multiple fields
COMMERCIAL:
In today's video, we're continuing with part four of our series on taking attendance using Microsoft Access. You're going to see how to set up totals in your attendance report by adding text boxes for total count, present count, and absent count, learning about the quirks of Yes No fields and why 'True' is stored as negative one. I will show you step by step how to use the Count and Sum functions, adjust formatting, and create class-wide summary totals in the report footer. You'll also see how to add a button to open your report from your form, and why preview mode is usually better than printing directly. At the end, we start talking about sorting records for a cleaner look, with more to come in the next video. 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 function was used to count the total number of records in a report section?
A. Count("*")
B. Sum("present")
C. Min("absent")
D. Average("attendance")
Q2. When storing Yes/No values internally in Microsoft Access, what does True correspond to?
A. Zero
B. Positive one
C. Negative one
D. Null
Q3. Why is it necessary to multiply the sum of the present field by negative one to get the present count in Access?
A. To convert null values to zeros
B. Because Access stores Yes as negative one and No as zero
C. To turn decimals into integers
D. To separate unique values only
Q4. What is the recommended practice for naming text boxes when you plan to refer to them in calculations?
A. Always name them meaningfully instead of using default names like text4
B. Leave the default names since they rarely matter
C. Only name the labels, not the text boxes
D. Use random characters to prevent confusion
Q5. How is the absent count calculated in the tutorial?
A. By summing the absent field values directly
B. By counting only unchecked checkboxes
C. By subtracting the present count from the total count
D. By multiplying the present count by two
Q6. Why should you avoid putting calculated totals in the page footer in Access reports?
A. Calculations in the page footer do not always work as expected
B. It slows down the entire report
C. The page footer cannot display text fields
D. The page footer only prints on the first page
Q7. In order to provide totals for the entire class, where should the summary controls be placed?
A. In the report header
B. In the detail section
C. In the report footer
D. In the page footer
Q8. What is the primary function of creating a button in the form, as demonstrated at the end of the tutorial?
A. To delete records
B. To print student names only
C. To preview the attendance report quickly
D. To export reports to Excel
Q9. When previewing the attendance report, what sorting issue was noticed in the video?
A. Names were sorted, but totals were missing
B. Records were not sorted by date within each group
C. Absences were calculated incorrectly
D. Present and absent counts were swapped
Q10. According to the video, what is a benefit of previewing a report before printing it?
A. It saves printer ink automatically
B. It allows review and confirmation before printing
C. It immediately sends a copy to the email recipient
D. It removes duplicate records
Answers: 1-A; 2-C; 3-B; 4-A; 5-C; 6-A; 7-C; 8-C; 9-B; 10-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 TechHelp tutorial from Access Learning Zone will continue our series on building an attendance tracking system in Microsoft Access. This is part four in the series, so I recommend starting with the first three parts if you are just joining in.
At this stage, our database is shaping up well, and the attendance report is almost ready. After reviewing the current state of our print preview, my next focus is to add some totals at the bottom of the report. These totals will be displayed using text boxes in the customer ID footer section.
To achieve this, I switch over to Design View and insert a text box in the appropriate footer area. I label this text box as "total count" to indicate its purpose. To ensure everything looks clear, I make the text black and set the background to transparent so the white background does not stand out. I move and resize the controls as needed for a cleaner layout.
I notice that these controls often come with a border from the toolbox, so I remove that by adjusting the border properties so that everything looks seamless.
Next, I configure the control source for the total count box by using the Count function. This will tally up the total number of records within the detail section above. I make sure the formula is entered properly so that it reflects the correct count for each group.
After saving the report, I check the results in print preview. Each group shows the expected total, which confirms the Count function is working as intended. I repeat this process for the formatting, cleaning up unused boxes and borders.
The next requirement is to show how many people were present. Rather than just counting the records, which would repeat the total count, I need to count only those marked present in the Yes/No field. It helps to know that Access stores Yes/No fields as zero for No (False) and negative one for Yes (True), unlike some other systems like SQL Server which use one for True. Keeping that in mind, I use the Sum function on the present field and then multiply the result by negative one to get a positive number for attendees marked present.
This formula calculates the present count accurately. Once saved and previewed, the correct numbers appear for each group. For presentation purposes, I bold the numbers and adjust the alignment of each field, using right or left alignment as needed for readability. Placing the fields side by side improves the overall look and makes better use of space.
The next step is to count absences, which is simple math: subtract the present count from the total count. Using copy and paste, I create another text box, name it appropriately, and set its control source to total count minus present count. I emphasize the importance of naming your controls for future references, as generic names like "text4" are not helpful when writing formulas or debugging.
After previewing again, the absence count is calculated and displayed correctly. I make final adjustments to the layout and organize the controls in the most logical order, putting emphasis on the absent count if that is the most important value for your reporting needs.
To summarize attendance for the whole class, I take advantage of the report footer section. Calculations do not work the same way in the page footer, so the report footer is best for overall totals. I copy the existing total, present, and absent controls to the report footer and rename them for this broader scope (for example, report total count, report present count, and report absent count). Their formulas remain the same since they apply to the whole dataset rather than just a single group.
Now, the report contains both per-group and total counts at the bottom, giving a complete overview. You might also consider adding more calculations, such as percentages of attendance, as further enhancements.
For ease of use, I add a button in my form to open this report directly in Print Preview mode. I find Print Preview to be the most useful, as it allows a final check before printing. However, if you regularly print the same report, you can set up a button to send it to the printer directly.
After wiring up the report preview button and labeling it accordingly, I test everything. Clicking the button pops up the completed report with all group and total calculations presented clearly.
At this point, I notice the records are not sorted as expected. The grouping on customer ID is already set, but additional sorting on the date or other fields may be necessary. Sorting ensures that both customers and their attendance entries appear in logical order. Arranging the report layout and sorting will be addressed in the next part of this series.
That wraps up part four of the attendance tracking system. I hope you found these steps helpful. You can find a complete video tutorial with step-by-step instructions covering everything discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Adding total count text box to report footer
Formatting text box appearance and alignment
Using Count function to total records in a report section
Copying and pasting text boxes in Access reports
Calculating present count using Sum and Yes/No fields
Multiplying Sum results to correct Yes/No totals
Naming text boxes for referencing in calculations
Calculating absent count using total and present counts
Formatting numeric fields as bold and right-aligned
Displaying totals for entire report using report footer
Renaming controls for report-level calculations
Opening report from a form with a command button
Previewing reports using command button wizard
Adding sorting levels and grouping in reports
Sorting report data by multiple fields
|
