Attendance
By Richard Rost
6 months ago
Tracking Attendance for Students and Employees In this Microsoft Access tutorial I will show you how to build an attendance tracking database from scratch, including setting up tables and queries for your students or employees, creating a form for daily attendance entry, and generating a report to show attendance counts over a date range. This is an expert level lesson that covers continuous forms, appending records for each day, and custom date formatting, all without needing any VBA programming. 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 database, attendance report, attendance form, class date field, student query, append query, sample attendance data, continuous forms, student combo box, date formatting, ISO date format, relational combo box, active students, attendance table design, present checkbox, main menu form, criteria in query, hide combo box ID, store student attendance, attendance roster, TechHelp free template, report by date range, format date yyyy-MM-dd, count present absent, conditional reports
Subscribe to Attendance
Get notifications when this page is updated
Transcript
One of the most popular requests I get is doing attendance, whether it's students or employees, or your dogs, making sure they're all home at the end of the day.
So we're going to do attendance today. I'm going to show you how to set up an attendance database, have a list of students, and then you can add them every day when it's time for class. Then we'll do a report so we can see how many times each student was present or absent.
When we're all done, it'll look something like this. You put your attendance date in here, show attendance. It'll show you if you've already done attendance that day. You come over here, you check whether or not your student was present.
Now, tomorrow you open the database. This is going to default to tomorrow's date, whatever date you open it. Hit show attendance. There's no attendance data for this day. Hit add students. It'll bring your students in, you just check which ones are present or not present. You can flip it whichever way you want to do it.
When we're all done, we'll make an attendance report. You click on the report, it brings that up. You can do it between two dates and you can see each student updates: they were present and absent. It gives you a count of each student. At the bottom, we'll give you a count of everybody. That's pretty cool stuff.
I'm going to walk you through building all of this from the ground up.
Now, this is an expert level video. What does that mean? Well, I consider expert the middle ground, basically intermediate between beginner and developer. So it's a little bit beyond the beginners, but you don't need any coding. We can do everything I just showed you with zero Visual Basic programming.
So it's all intermediate level stuff. Here are the prerequisite videos that you should watch first before trying to follow this one.
First, watch my blank template video, which is how I explain how I build the database that I use for most of my TechHelp videos, including this one. A basic main menu, a customer list, customer form, and so on.
You should know how to concatenate two strings together. You should know how to build basic queries and use query criteria. You should know how to work with continuous forms. It is vital that you understand how to work with relationships between multiple tables. Students and attendance are going to be related. You have to understand that relationship.
You should know how to make relational combo boxes. We're going to make our combo box to pick the student. You should understand how to format your dates properly. I personally like to use the ISO date format. It looks like this: year, month, day, because it's unambiguous no matter what country you're in. So watch this if you're curious about how to use this.
It is very important that you understand how to use append queries. We're going to use an append query to copy the students into the next day's attendance roster. So make sure you watch this video and make sure you know how to get a value from an open form. That's how we're going to get the date criteria from our main menu form into our other stuff.
Now these are all free videos. They're on my YouTube channel. They're on my website. Go watch all of these and then come on back. Don't complain that I didn't show you how to do some of this stuff. It's all in the prerequisite videos. There might be some more later on. I'll mention them if there are.
All right. Let's get started.
So here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want one. Since I already have customers in here, we're going to just use customers as students. So when I say students, I'm talking about customers. They're already in here. We're just going to use these guys. I am going to use the is active field to indicate which students are enrolled in my class.
Now, if you want to talk about a more advanced database, where you have multiple classes and multiple teachers and students for different classes, yes, we'll talk about that later. For now, let's just focus on one class.
So let's make a student query. Create query design that shows just our active customers. That's our list of students.
Let's go back to the name in CustomerT. Let's bring in customer ID. Down here, instead of just first name and last name, let's make a student name field, student name. That's going to be first name and a space and last name. That's string concatenation here. I'll zoom in so you can see it better. Shift F2.
Student name: first name and a space inside of double quotes and last name. We'll need that is active field, way down there in the bottom, and we're going to set a criteria to true so I only see the active customers. Those are my students. Let's save this (Control S) as MyStudentQ. That's my student query. If you want to sort it, you can. It doesn't really matter. Run it.
There we go. We have way too many active students. Let's whittle this down to about six. So I want to see here, we'll get rid of you, you, you, you, you, you, and you. I want James Kirk. I like James Kirk in there. Let's keep Mr. Spock. We have like six.
So now if I run it, there we go. We have six active small group sessions. That's what I used to do: small group sessions. I have my own computer training, where I had a max of eight. I took eight students at a time. Usually, it was six or seven.
Now we need a table where we're going to store our attendance data. Each class for each student is going to get an attendance bit of data.
Create table design. We have AttendanceID. That's our auto number. Yes, it's attendance. Sometimes I always want to put an E here and I think to myself, "For attendance, we're going to the dance, we're going to attend the dance." That's how I remember it. Stupid little thing.
Now that's our primary key. I always forget to click the thing, but you can click on the thing if you want to. Otherwise, it'll ask you. CustomerID, that's our foreign key. So this is a number of type long integer, and that'll be set for us automatically.
Let's put a class date. Now you can do a class date, class date/time, whatever you want to do. Generally, I just put the date in here, so I'm going to make the default value =Date(), just like that. That'll use the date function to put the current date in there.
If you want the time as well, like if you really care up to the minute when the attendance was taken, some places do. They want to know when this teacher actually did attendance. That's fine. You can store the time in here too. We're going to build the database assuming it's also got times, but I'm just going to work with dates. They're much easier, and they just look more pleasant.
Then we've got present: was the student present or not? Yes or no. Now, the default value for this is up to you. If you want to default them all to no, and have the teacher check to make sure they were in their seats, great. If you want to default that to yes, that way the teacher just has to check who's not there. Again, totally up to you. That's your use case. I'm just showing you how to put the Legos together.
So let's save this as AttendanceT. That's my attendance table. Close it up.
I always find that stuff is easier when you've got data in the tables to work with, just some sample data. So I'm going to open up a student query so I can see who the students are, so I have their IDs.
The attendance table is going to look like this. Let's say we're working with today's date. Today is actually May 30th. I'm recording this for May 29th because I got really busy and didn't get a video out, so this is actually the first retroactive video I've done in a long time. This is going to be published on the 30th, but it's for the 29th.
So the first day, let's do it for 5/30. So James Kirk, 5/30, was present. Mr. Spock, 17, same date, was present. Mr. Barkley, same date, was not present. Me, I was also playing hooky. Wesley Crusher, he's a good boy, he was present. Finally, Will Riker was also present.
So that's how it's going to look when it's all said and done, and every day you'll have another set of six entries or however many students you have, and you'll just check these boxes.
Save changes, yes, and close it. That's really all we need for now for our tables.
Now let's work on our attendance form. Our attendance form is going to be a continuous form. In the TechHelp free template, I've got a blank one here that I use as basically my template. I'm going to copy and paste that and call it AttendanceF, my attendance form. Right-click, design view. Open up the properties for the form. Set the record source to that attendance table that we just made.
I am going to delete these fields, and I'm going to delete one of these labels up here because I'm in the habit now with small forms like this, I just make one big label going across the top, and I just put the fields in there accordingly.
Now let's open up the Add Existing Fields box. You don't really need the AttendanceID on this form. In fact, generally, I really only put IDs on forms if I need their value or if I'm doing it in a beginner class to teach my students how IDs work. Usually you don't need them on the forms.
But we do need the customer, but I'm not going to bring the CustomerID from here because I don't want it as a text box. I want it as a combo box. We'll use the wizard just for that.
I do need the class date/time. Click, drag, drop it in the detail section. Get rid of the label that comes in with it and then slide this guy over here like that. Make it a little tiny bit bigger, like so.
We need a box for present. Drop that over here, and again, delete the label that comes with it. Slide it over there. Actually, this is probably a little bit too big. If you're going to put time in here, it needs to be bigger. If not, if I'm just doing dates, you can do that. We're going to do date and then I'm also going to show the day of the week. I'll show you how to do that.
Next, we need our student. For that, we're going to make a relational combo box. That's right. Click, drag, drop into the detail section. I want the combo box to get the values from a table or query. Where is the list of students? That's in the student query, right there. That's our list of students.
Next, I need CustomerID and student name. You don't need to bring the is active into here.
Do you want to sort it? Student name is fine.
Now, since this is based on a query, we don't get the little box that says you can hide the ID fields. We have to manually hide the ID field just by making its width zero. Go past it like that to make sure you're at zero. That's what it's going to look like.
What is the bound field?What is the field that actually has the value that you wanted it? Well, that's the ID.
Next, do you want to remember it for later use or store it in a field? Well, in this case, I want to store it in a field. I want to store that customer ID in the customer ID in the attendance table. I am picking a customer ID, which is my student ID basically, and I am storing it in the customer ID field of the attendance table.
Next, what label would you like for it? It doesn't matter. We're going to delete it anyway, and then hit finish. All right, there is that label. Goodbye. Slide this up into place like so. Move it about like that. Looks good.
One thing I don't like about the combo box wizard, and this is one of my Access pet peeves, is that it doesn't give you the option to name that box. It calls it combo 62. I am going to call this my student combo, and yes, it's bound to the customer ID. That's fine.
Let's shrink this space up there. Save it. Close it. Let's open it and see what we got. Looks pretty good. A couple little things. First of all, let's left align that date. I don't like how the date picker just kind of hangs out there. We really don't need the date picker for this form. We're never going to set that in here. We'll do it somewhere else. I'll show you where.
So, I am going to turn off that date picker. It is under data--nope, I apologize, my bad--it is under format. Sometimes even I forget these things. I don't use this that often. Then turn this guy off to never.
See, even I use Access all day long, and I still forget where the stuff is on menus. I am going to go into the format here. Now, you could just go with short date if you want to, or you could put your own custom date in here. I like to do something like this. Watch: yyyy-MM-dd - ddd.
Here, I'll zoom in so you can see it better. Shift-F2: yyyy-MM-dd - ddd.
Anybody know what that is off the top of your head? Well, this is my favorite ISO date format: year, month, day. The most logical of all date formats. Then I put a space and a dash there, which is a literal space and a dash. This is the three-character abbreviation for the day of the week. That's often very handy to have when you're dealing with calendars or attendance or any of that kind of stuff. You want to know what the day of the week is.
Save it. Close it. Open it. Oh, look at that. For some reason, I forgot to left align it. Let's do that. Click. Format. Left align. That was what I went in there for.
All right, one more time. Oh, looks so much better now. You can quickly and easily see it's Friday.
Let's put the labels across the top. What I do is I just take this guy, stretch it all the way out as far as you need it like that. This is going to be class dates. You can put class date time in there if you want to. A bunch of spaces holding down the space key. Not fancy.
Student, space key, and then present over here. That's all. I just find that's easier than dealing with multiple little labels, and you have to move them around. That's a big pain.
Attendance. There we go. Beautiful.
So we have this all set up. Now, what we're going to do next is we're going to use this date field we have over here and make this the default value for the next guy that goes in here. So I can change this, and then the next dates to come in here will be whatever date that is.
We're going to make this button open up this form. Then we're going to make our little button down here to add the students for the date that is in this box. So, I can change this to the 31st and then students come in automatically.
We're going to do all of that in my TechHelp video. So tune in to my TechHelp video next time, same bat time, same bat channel.
Members, you can watch it right now because I'm going to record it in just a minute.
For those of you who are my regular viewers, I know today this video is being released for Thursday, the 29th of May, 2025. Tomorrow's Friday. I normally do Quick Queries on Fridays, but I'm going to do this video tomorrow instead. We got pushed back a day because of Memorial Day. I might do Quick Queries on Saturday. If not, I'm going to push it to next week. But I'm definitely going to continue this with part two tomorrow, Friday.
So that's going to do it, folks. That is your attendance video, your TechHelp video part one for today. Hope you learned something.
Well, someone's beaming in. Live long and prosper, my friends. I'll see you tomorrow for part two.
TOPICS:
Setting up an attendance database
Creating a student query for active students
Concatenating first and last name for student name
Filtering students by active status
Creating and designing the attendance table
Setting default date value for attendance entries
Using yes/no field for student presence
Entering sample attendance data
Building a continuous attendance form
Adding class date and day of week formatting
Creating a relational combo box for students
Binding combo box to CustomerID in attendance
Customizing combo box properties and naming
Formatting date fields to ISO format with day abbreviation
Arranging labels and fields on the attendance form
Disabling the date picker on the attendance form
Aligning form fields for visual clarity
COMMERCIAL:
In today's video, we're learning about setting up an attendance tracking database in Microsoft Access, perfect for students, employees, or anyone you want to keep tabs on. I'll show you how to create student records, add daily attendance entries, and build reports that count each student's presence and absence between any dates you pick. We'll set up continuous forms with relational combo boxes and use queries to get your active students filtered just right. No programming knowledge is needed, and I'll walk you through everything step by step, but you should be comfortable with the basics like table relationships, simple forms, and queries. 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 goal of the attendance database described in the video?
A. To track each student's attendance by date and generate reports on their presence or absence
B. To store teacher evaluations and grades for students
C. To enroll students in multiple classes and assign them grades
D. To schedule classroom events and send automatic reminders
Q2. Which field in the customer (student) table is used to indicate if a student is currently enrolled in the class?
A. LastName
B. FirstName
C. IsActive
D. CustomerType
Q3. What is the relationship between the student table and the attendance table?
A. One-to-one
B. Many-to-one (many students for one attendance record)
C. One-to-many (one student with many attendance records)
D. Many-to-many (students to attendance via a junction table)
Q4. Why does the instructor prefer the ISO date format (yyyy-MM-dd)?
A. It is required by Access
B. It is the most familiar for US users
C. It is unambiguous across all countries and easily sortable
D. It saves disk space
Q5. What type of form is used for entering and displaying attendance records?
A. Single Form
B. Split Form
C. Continuous Form
D. Datasheet Form
Q6. What is the default value set for the class date field in the attendance table?
A. 1/1/2000
B. "Today"
C. =Date()
D. 0
Q7. Why does the instructor recommend using a combo box for selecting the student on the attendance form?
A. So you can type any student name directly
B. To select from a list of students derived from the active students query
C. To display all student information at once
D. It is required by Access
Q8. What is the role of an append query in this attendance database workflow?
A. To delete old attendance records
B. To copy the list of students into a new day's attendance roster
C. To summarize attendance totals in a report
D. To concatenate student names
Q9. When setting up the combo box for student selection, why is the customer ID field hidden?
A. IDs are not needed for selection, only names are
B. It keeps the form from crashing
C. IDs are sensitive information and must be hidden for privacy
D. The instructor prefers alphabetical order
Q10. Why should students watch the prerequisite videos before attempting this tutorial?
A. They are required for access to the free template
B. The tutorial assumes knowledge of basic Access concepts and relationships
C. They contain special discount codes
D. To learn about SQL programming
Q11. What are some recommended concepts to know before following this tutorial?
A. Creating PowerPoint presentations and Excel charts
B. Concatenating strings, building queries, working with relationships and combo boxes
C. Building websites with HTML and CSS
D. Programming in Visual Basic for Applications
Q12. What advantage is there to adding sample data early, before building forms and reports?
A. Sample data slows down database development
B. Sample data allows you to test and view form functionality more easily
C. It makes the database look more complicated
D. It is required to run append queries
Q13. What feature does the attendance report offer, according to the video?
A. It shows only present students for the current day
B. It can display counts of each student's presence and absence between two dates
C. It deletes absentee records automatically
D. It only works for employees, not students
Q14. Which field in the attendance table is used to mark whether a student was present or not?
A. IsActive
B. CustomerType
C. Present (Yes/No)
D. Status
Q15. How does the instructor configure the day of the week to be displayed along with the attendance date on forms?
A. By using a separate day of week field in the attendance table
B. By formatting the date field as yyyy-MM-dd - ddd
C. By appending day name in a report only
D. By using a lookup table
Answers: 1-A; 2-C; 3-C; 4-C; 5-C; 6-C; 7-B; 8-B; 9-A; 10-B; 11-B; 12-B; 13-B; 14-C; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone covers one of the most requested topics I receive: tracking attendance, whether for students, employees, or even pets. Ensuring you have everyone accounted for at the end of the day is a common need, so today I will guide you through setting up an attendance database in Microsoft Access.
We will start by setting up a list of students, then learn how to record attendance each day. I will also show you how to generate a report to review how many times each student was present or absent. At the end of the process, you will be able to select an attendance date, check to see if that day's attendance has been entered, and then mark each student as present or not present. When you open the database on a new day, it will default to today's date, and you will be able to add everyone for the new session with just a couple of steps. This system can be easily tailored to fit your attendance tracking process.
Once the data is in place, you will be able to run an attendance report for any date range, showing each student along with their counts of present and absent days, as well as a total summary for all students. This will provide a clear and organized view of your attendance records.
Throughout this tutorial, I will walk you through the entire workflow from the ground up. This lesson is what I consider to be at the expert level, meaning it sits between beginner and developer. However, all the tasks covered here can be accomplished without any Visual Basic coding. You only need to understand the intermediate concepts in Microsoft Access. All features shown, such as forms and queries, can be created using Access's built-in tools.
Before you begin, there are a few prerequisite skills you should have for this project. First, I suggest you watch my blank template video to see how I set up my basic TechHelp database, which we will continue using here. You should already be comfortable concatenating two strings together, building basic queries, using query criteria, and working with continuous forms. It is essential that you understand the fundamentals of table relationships in Access, since students and attendance records will be connected. Knowledge of creating relational combo boxes will also be important, as will understanding date formatting (such as the ISO format: year-month-day). If you are not already familiar with append queries, make sure you review how they work because we will rely on them to load students into the attendance roster for each new date. Finally, you should know how to retrieve a value from an open form, which is necessary for passing the date selection from the main menu to our various objects.
All of these prerequisite lessons are free and available on both my YouTube channel and my website, so please review those first if you need a refresher.
Let's move on to the design. For this walkthrough, I am working with my TechHelp free template. You can download it from my website if you like. In the sample data, I already have a customers table, which I will use for students. When I refer to students in this tutorial, you can think of them as customers in my database. I am using the "is active" field to indicate which students are currently enrolled. If you are interested in building a more complex database, such as one with multiple classes, teachers, or more advanced enrollment scenarios, I discuss those in other lessons, but for now we will stick with a single class setup.
To prepare a list of active students, I use a query based on the customers table, filtering it for active records. I create a student name field by joining first and last names together (concatenation), and then include the necessary fields such as student name and the active status. Filtering for only those marked as active gives me my student list, which I save as MyStudentQ.
For a small group class, I find that about six students is a good number. I narrow down my query so I only have six active students, as I used to teach small groups in my computer training sessions.
Next, it is time to set up the table that will store attendance data. Each attendance entry will be tied to a specific student for a specific date. I create a new table with fields for AttendanceID (the primary key), CustomerID (the foreign key linking to our student), the class date, and a "present" field to indicate their attendance status (yes or no). You can decide whether the "present" field defaults to yes or no depending on your workflow. If you need to track time as well as date, you can add a time field, but for simplicity, we will focus on using just the date.
After saving this as AttendanceT, I recommend putting some sample data into it to make testing easier. Using the student IDs from our student query, I fill in attendance records for a specific day, indicating who was present and who was not. Once this is done, you have a working example of what daily attendance records will look like in your table.
The next step involves building the attendance form. We use a continuous form, making a copy from the template included in the TechHelp Free template database and renaming it AttendanceF. The form's record source should be the AttendanceT table. On this form, there is no need to show the AttendanceID. The important fields are student (as a combo box), class date, and present. For the student selection, set up a relational combo box that uses our MyStudentQ query so users can select from only currently active students. Make sure the combo box stores the CustomerID in the Attendance table.
By customizing the date display, you can format it in ISO style (year-month-day), followed by the abbreviated day of the week, making it clear and consistent for users regardless of where you are in the world. Adjust the alignment and label fields to keep the form clean and readable.
After arranging the layout and labels, you can further refine the user experience. For example, turning off the date picker (since dates will not be set directly from this form) and making sure the date is clearly displayed and left aligned improves overall usability. Using a single label across the top with fields spaced out using the space bar often speeds up form design and keeps things uncluttered.
With the basic attendance form complete, you can now set up attendant features like defaulting the class date for each new entry and automating the process for adding all active students to the attendance roster for each day. These features, along with integration through menu navigation and button actions, will allow you to efficiently take attendance each session.
In this lesson we have covered part one: creating the foundation for your attendance tracking system using forms, tables, and queries. Part two will continue this process and expand the functionality further.
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
Setting up an attendance database
Creating a student query for active students
Concatenating first and last name for student name
Filtering students by active status
Creating and designing the attendance table
Setting default date value for attendance entries
Using yes/no field for student presence
Entering sample attendance data
Building a continuous attendance form
Adding class date and day of week formatting
Creating a relational combo box for students
Binding combo box to CustomerID in attendance
Customizing combo box properties and naming
Formatting date fields to ISO format with day abbreviation
Arranging labels and fields on the attendance form
Disabling the date picker on the attendance form
Aligning form fields for visual clarity
|
