Attendance 3
By Richard Rost
6 months ago
Tracking Attendance for Students and Employees Part 3 In this Microsoft Access tutorial, I will show you how to create a filtered attendance report that displays each student, their attendance dates, and groups the data to clearly show who was present and when. We will add start and end date filters to the form, build a supporting attendance query, and design a well-formatted report grouped by student. This is part 3. 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, attendance form, attendance query, student grouping, class date range, report footer totals, grouping levels in report, class absences, present count, report design view, date criteria, VBA refresh, report formatting, custom report footer, student attendance records
Subscribe to Attendance 3
Get notifications when this page is updated
Transcript
Welcome to another TechHelp video brought to you by accesslearningzone.com. I'm your instructor, Richard Rost. Today is part three of my attendance series where I'm teaching how to take attendance like a teacher.
If you haven't watched parts one and two, go watch those first. We did those last week, and then come on back here for part three.
So, we got our attendance table built. We've got our attendance form where we can see everybody. We have a button here to add students for that date. After you click the button, you have to close this and then reopen it again. Then there they are, right there, 6/2. That's today's date.
Yes, I'm going to show you how to make it so that we don't have to close and reopen the form ourselves. But that's going to involve a little VBA programming, so we're going to get there. First, I'm still trying to get through the expert level stuff which doesn't require any programming. Then we're going to add some extra enhancements that do use some VBA.
Today, we're going to make a nice little report to show all this stuff. We've got Friday here, Saturday, let's say these people were in class on Saturday, and then on Monday, these people were in class.
Now I want a nice report that shows each student and what days they were present, and then a count of absences, and it'll look nice. Let's build that today.
But first, a couple of prerequisites. First, I want you to watch my form footer totals video. I know this video talks about form footer totals, but the same thing works in reports. Whether you're in a footer in a report or a footer in a form, it doesn't matter. It's the same kind of stuff, so go watch this.
Also, go watch my video on grouping levels in real reports because we're going to group it by each student. These are both free videos. They're on my website and they're on my YouTube channel. Go watch those and then come on back.
First, we're going to need to add a couple of fields to our form here because we're not going to do attendance of everything in the database. I want a start date and an end date, so I'm going to copy this guy, copy, paste (Ctrl+C, Ctrl+V), and come down here. This will be our start date. And we're going to copy and paste that guy again, and this will be our end date.
Now, we have to give the boxes names. Don't forget, those were just the captions that I just edited. We have to change the boxes, so double click. This guy is going to be "start date," and this guy is going to be "end date."
If you want to put some default values in here, that's fine too. Let's say normally you're doing like the past week, so you could say in here for the default value, let's make this one today's date minus seven. Then for the end date, let's do today plus one because I know sometimes if it's a Friday, you want it to go to Saturday.
You want to include everything up to that date, or default it to today, and then you just have to make sure in your criteria that you add one to it so you get all those dates. Let's do that, let's do date minus seven and then date.
Save it, close it, open it. Looks pretty good. Nothing crazy, you're new here.
Now I'm going to make a query that's going to show me all the attendance records between these dates. Create - Query Design. What's going to be in here? I'm going to bring in my attendance data, and then I'm going to bring in my student queue because my student queue has my student information in it - just the students in here.
We're going to join this together by customer ID. If it doesn't make the join for you, sometimes when you're working with a query, it doesn't automatically make that join, so we're just going to go click, drag, drop. Everyone that's in this table should also be in this table, so you shouldn't have to worry about your join type here.
I want to see the customer ID, I want to see the class date time (that's where we're going to put our criteria in just a minute), I want to see if they're present, and I also want to bring in their student name because I want to see that on the report.
Now, that criteria we're going to get from these fields back here that we just created. The criteria for class date time: I'm going to come in here to criteria, I'm going to zoom in (Shift+F2). It's going to be greater than or equal to Forms!MainMenuF!StartDate and less than, not less than or equal to, just less than, Forms!MainMenuF!EndDate + 1. Is that plus one?
If I put in Monday through Friday, you want it to include all of Friday because they might have times in there. If they have 9 p.m., you want to make sure, because end date by itself would be just at midnight, so you want to add one to it and make it less than that. So you're actually saying give me everything from Monday at midnight all the way up through Saturday at midnight, but not including Saturday at midnight. That's why we do it this way - less than this date plus one. You want to assume they're typing in Friday when they really want all of Friday.
Hit OK.
Save this as "MyAttendanceQ."
There we go. There's all of our dates, and if you want to just test this, you can change this guy to "31". Make sure you leave the field. If you just change the date here and hit the button or run the query, it doesn't update that until you leave that field. It's another thing you can control with VBA, as you can do a refresh of this data before opening up the report.
Now if I run it, you'll see just "31" and the second. That's perfect. I'm going to put this back to "26" or whatever it was before so we see everything. There's good; we got three dates for everybody. Let's make the report now. We're ready.
In my TechHelp free template, I have a blank report down here that I use for making new reports. It's just got my margins and my page width and all that stuff set up so I don't have to keep doing it in every report that I make. I strongly recommend you do something like that. I'm going to copy and paste this to "MyAttendanceR" for "report".
Right click, design view, and here we are. It's already set out to the width of an 8.5 by 11 sheet of paper, got my margins all set. I'm going to open up the report properties, and I'm going to bind this by changing the record source to that MyAttendanceQ that I got. That's where it's going to get its data from.
Now I can add fields to it. I'm going to go to report design, and then "Add Existing Fields", and there they are. I usually keep this guy around for formatting, but we don't need it right now. I'm going to bring in - you don't really need the customer ID for this unless you want to see it. If you've got two Joe Smiths, you might want a customer ID for clarity. I'm going to skip it for now. I'm going to bring in these three guys, which is - click on the first one, hold the shift key down, click on the last one, let the shift key go, and then click and drag and drop. There we go. There's all three of those things.
I'm going to make these guys go side by side like that just to see what we're dealing with here. Put that there, put that there, shrink up that detail section.
Save it and do a print preview. I'm going to right click here on the bar and go to print preview. See what we got.
First things first, let's get rid of these boxes. I hate the boxes. Back to design view. Close that field list, we're done with you. Click on that, shift click on that, go to "Shape Outline," set that to transparent. That should look a lot better.
Take a quick peek. I got print preview up here on my quick launch toolbar. That looks a lot better now.
Next, I want to group this based on the student so every student and all of their dates show up together. How do we do that? Close print preview.
You could group on student name, but again, you run into that problem where if you've got two Joe Smiths, then it's going to put them together. I like to group on customer ID. Go over here and do "Group and Sort," then add a group based on customer ID.
Also, turn a group footer on because it gives you a group header, but I want to click on "More" and then pick "With a Footer Section" so we get a footer there too.
I'm going to squeeze up that header, they were from the report header, the page header, and the page footer, report footer. We might do something with those later, but for now we don't need them.
So here's the customer ID header. I'm going to move the student name up here so it's grouped by each student, because a customer and student is the same thing from lesson one. We could probably get rid of that label too. Delete the label, slide that over there like that.
Now, the class date times are going to be here. I don't need the label next to it. Maybe put the label above it, slide this over here, maybe make this a little bit bigger. This definitely has to be bigger to fit the class date time. There we go.
I'm just going to take that label off of there, cut it off, click up here, and paste it. Then slide it over here like this. This will be "Class Date/Time" or just "Date," whatever you got in there. Copy, click, paste it again, copy, paste. Sometimes you can copy-click-paste, sometimes you can't. Then "Present".
We're going to do that, left align this because it's a date, so it's going to come in right aligned. I like it all left aligned.
We can turn off this grouping in total right here. Don't click this, as this will actually remove that and delete that group. You want to click on that guy to close the pane.
Save it, and you can close it and reopen it if you want to. Right click, print preview. And look at that, there we go. Looking pretty good.
I hate this alternating band stuff. I really don't like that. So let's do this: go into design view again, for the customer ID header, double click on this guy. It will bring up the properties. Go to "Format - Alternate Back Color", set that to "No Color". Same thing with the footer.
Now the footer, I want to actually change the footers. Let's set this to "No Color" and let's make this just slightly gray. Click on the dot dot and go with like a light gray. That's where we're going to put our totals – they'll break up each student a little bit. Maybe we'll make this bold and just a smidge bigger, maybe go 14 point like that.
All right, starting to get there. See, this is one of my exceptions to the save-it-close-it thing. Reports, I tend to just be lazy and go in here and go to print preview because it's a pain to switch between them the other way.
That looks a lot better. Richard Rost, James Kirk, William Riker, Worf, Red Sparkly, Mr. Spock, class date/time and present there. Looking pretty good.
Maybe shorten it up just a little bit, maybe get rid of just a little bit of extra space in there. Let's see what this looks like. Save that, print preview. There we go. Now we got everybody on one page. That's perfect. You can put a line in there. There's all kinds of stuff you can do now.We're ready to put our totals in here. I want a count of how many items total, so there would be three for each of them. How many were they present for, and how many absences do they have? Then we will do the whole thing and total up the entire class.
We will do all of that in tomorrow's video, so tune in tomorrow, same bat time, same bat channel.
We have a bunch of other stuff to do as well. I got some good ideas from some students, and we will throw them in and make even more videos. Some of it is going to be some VBA stuff.
Again, a lot more cool stuff is coming, members. I am recording right now, so you can probably watch it tonight.
All right, that's going to do it for part three. Hope you learned something. Live long and prosper, my friends. I will see you next time.
TOPICS:
Adding start date and end date fields to the form
Setting default values for date range selectors
Creating a query to filter attendance by date range
Joining attendance data with student information in queries
Applying date criteria using form field values in queries
Saving and naming attendance queries
Testing date filters in queries
Copying and modifying a blank report template
Binding a report to a specific query as a record source
Adding and arranging fields on the report
Removing outlines from report controls
Grouping the report by student using customer ID
Adding and configuring group header and footer sections
Moving labels and formatting report fields
Adjusting alternate background and footer colors
Formatting group footers for summary sections
Previewing and adjusting report layout for optimal fit
COMMERCIAL:
In today's video, we're continuing with part three of the attendance series where you will learn how to build a report that displays each student's attendance with date ranges, counts, and grouping. We'll discuss adding start and end date fields to filter results, setting default values, joining attendance and student data, and grouping reports by student ID for accurate results. I'll show you how to clean up the design, adjust formatting, and set up headers and footers for a professional look. 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 primary purpose of the report being created in this tutorial?
A. To display each student and the dates they attended, along with counts of absences and presence
B. To automatically enroll students for future classes
C. To update student records with grades
D. To handle payment processing for coursework
Q2. Why is it important to group the report by customer ID instead of student name?
A. Because customer ID allows for sorting by alphabetical order
B. Because multiple students can have the same name, but customer IDs are unique
C. Because customer ID includes course details
D. Because student names cannot be grouped in Access
Q3. What fields are added to the form to allow filtering attendance records?
A. Status and location
B. Grade and subject
C. Start date and end date
D. Teacher name and class time
Q4. How does the query ensure all desired dates are included when filtering by end date?
A. By using less than or equal to EndDate
B. By using greater than or equal to EndDate
C. By using less than EndDate plus one day
D. By using greater than EndDate plus one day
Q5. What is recommended to help avoid repeatedly setting up margins and page widths when making new reports?
A. Use Access templates for students only
B. Save a blank report with desired settings to copy for new reports
C. Change the default printer settings before each report
D. Use macros to set margins each time
Q6. What should you do if a join between tables does not appear automatically in the query design?
A. Ignore the missing join and continue
B. Add the join manually by clicking and dragging the fields
C. Restart Access
D. Delete one of the tables from the query
Q7. Why does Richard add one day to the EndDate in the query criteria?
A. To show the first day of the next week
B. To exclude holidays from attendance
C. To include all times within the EndDate up to midnight
D. To prepare data for VBA programming
Q8. What formatting step is demonstrated to improve the visual appearance of the report?
A. Inserting a school logo
B. Setting shape outlines of boxes to transparent
C. Adding student photos beside names
D. Applying conditional formatting to grades
Q9. What section of the report is used to display totals for each group of students?
A. Report header
B. Page footer
C. Group footer
D. Detail section
Q10. What two prerequisites does Richard recommend watching before proceeding with this report video?
A. Form footer totals and grouping levels in real reports
B. Calculated fields and subreports
C. Query optimization and VBA basics
D. Importing data and exporting reports
Answers: 1-A; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-B; 9-C; 10-A
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 continues our attendance tracking project with part three of my attendance series, showing you how to take attendance just like a teacher would.
If you have not seen the first two parts yet, be sure to review them before continuing. In those previous lessons, we set up the attendance table and the form that lets us view and add students for each date. Currently, after adding new students, you have to manually close and reopen the form to see the changes. I will eventually show you how to refresh the form automatically, but that requires some VBA. For now, we will continue working at the expert level with features that do not need programming, and we will add VBA enhancements later.
The focus for today is designing a clean and useful report that displays attendance information. For example, I have data showing which students attended on Friday, which ones were present on Saturday, and who showed up on Monday. The goal is to create a report where each student is listed alongside each day they were present, with absences counted as well. The report should be well-organized and visually clear.
Before we start, there are a couple of prerequisite concepts you should understand. First, watch my form footer totals video. Even though it is about forms, the same principle applies to report footers. Secondly, check out my video on grouping levels in reports, since we will use grouping to organize the data by student. Both of these videos are freely available on my website and YouTube channel.
The next step in our process is to prepare the form by adding date fields that specify the date range for the attendance we want to view. We do not want to report on all attendance data, just a selected period. You will need both a start date and an end date control on your form. Assign meaningful names to these controls, such as "start date" and "end date." If it is convenient, you can provide default values, such as today's date minus seven days for the start, and today's date for the end. This makes reviewing the past week easy, but you can adjust as needed for your workflow.
Once these controls are in place, save and close the form, then reopen it to see the changes. Now we need a query that displays attendance records falling within the selected date range. To do this, create a new query and add both the attendance data and your student information (from your student queue table). Join these tables on customer ID. If Access does not automatically create the join in your query, simply drag and drop the fields to connect them.
Include the fields you want to display in your report: customer ID, class date and time, present status, and student name. To filter the query based on the selected date range, use criteria that reference the form's start and end date fields. Set the class date time criteria to be greater than or equal to the start date, and less than the end date plus one day. This approach captures all attendance from midnight on the start day through the end day (including times late in the day), without excluding any entries on the end date.
Save this query as "MyAttendanceQ." You can test it by changing the end date and verifying that you get the expected results. Remember, if you change a date on the form, be sure to leave the field so the change is recorded before running your query. This process could also be improved in the future with VBA to refresh the data dynamically.
With the query working, we are ready to build the report. In my TechHelp free template, I maintain a blank report with preset margins and page width, which I recommend doing to save time. Copy your template report, rename it "MyAttendanceR," and open it in design view. Bind the report to your new query by setting the record source property.
Next, add your fields to the report. Drag in the relevant ones, arrange them neatly, and resize as needed. After laying out the detail section, save and preview the report in print preview mode. To clean up the appearance, remove any default borders or outlines from the controls.
To organize the report by student, use the grouping tools to group by customer ID rather than student name. Grouping by customer ID ensures unique students, even if you have multiple people with the same name. Add a group footer as well as a header, then move the student name up to the group header area. Remove or reorganize labels for a cleaner look and broaden columns as necessary to fit your data.
Adjust the formatting as you go. For example, you can turn off alternating row colors in the group header by setting the alternate back color to "No Color," and do the same for the group footer. You might want to use a light gray background for the footer to help totals stand out, and increase the font size or make the footer bold for emphasis.
After fine-tuning, save and preview the report again. Each student's attendance records for the selected period should now appear together, clearly separated for easy reading. You can compact the layout further if needed so that all information fits nicely on one page.
The last step involves calculating totals: how many sessions each student attended, how many absences occurred, and overall group statistics. We will cover building these totals in the next lesson in the series, along with additional features suggested by students and some VBA enhancements.
That wraps up part three of the attendance tracking series. I hope you found this lesson helpful. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Adding start date and end date fields to the form
Setting default values for date range selectors
Creating a query to filter attendance by date range
Joining attendance data with student information in queries
Applying date criteria using form field values in queries
Saving and naming attendance queries
Testing date filters in queries
Copying and modifying a blank report template
Binding a report to a specific query as a record source
Adding and arranging fields on the report
Removing outlines from report controls
Grouping the report by student using customer ID
Adding and configuring group header and footer sections
Moving labels and formatting report fields
Adjusting alternate background and footer colors
Formatting group footers for summary sections
Previewing and adjusting report layout for optimal fit
|
