Introduction

Welcome! Track Attendance with Append/Delete

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Expert Level 15. In this course we will continue working with action queries, focusing on append and delete queries. We will begin with tracking attendance for students or employees in training classes, including setting up tables, managing many-to-many relationships, designing forms and reports, and automating tasks with macros. We will also walk through archiving old contact information by copying and deleting records using append and delete queries. This class builds on concepts from Expert Levels 13 and 14, and covers necessary skills for effectively managing student attendance and basic data archiving in Microsoft Access.

Navigation

Prev: X15
Next: Lesson 01

Keywords

Access Expert, action queries, append query, delete query, tracking attendance, attendance database, many-to-many relationship, attendance form, attendance report, student attendance, archiving contacts, embedded macros, student forums, sample databases

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 15 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are continuing our coverage of action queries. We'll be working with more append queries and we'll be learning about delete queries.

We're going to start off with a very popular topic, something I get asked about all the time: tracking attendance, whether it's students in classes or employees in meetings. These techniques will work for any kind of situation where you have to track attendance.

We'll start off by tracking customers in classes for our fictional PC resale company. We're going to do computer training, so we'll track each of our customers in different computer classes. We'll set up a many-to-many relationship so each of our customers can be in multiple classes and vice versa. We'll also allow each customer, which might be a company, to send multiple employees for training.

Then we'll set up a form to track attendance for each of those customers in each session of the training classes. We'll create an append query to take all of the students that are registered and put them in the day's class session. That way, all the instructor has to do is check the box indicating whether they're present or absent. This keeps the instructor from having to type in the names every time there's a class.

Next, we'll learn how to edit a button macro so that the append query can run automatically. The instructor can click on one button. All of the students are added to the day's class. All of the warning messages are turned off, so you're not bothered with those.

Then we'll create two printable reports. One will show attendance by date so you can see which students were missing from which class. The second will show attendance by student so you can get a list of each student and what days he was absent.

Next, we'll learn about delete queries and how to delete records from your tables automatically. The example we'll use is archiving old contact information from our customer database. If you talk to customers every day, after several years, you might have extra information in there that you don't want to delete, but you want to move it so it's not in your main customer database.

We'll use an append query to copy old records to a new table, then we'll use a delete query to remove them from the original contact table.

This class follows Access Expert Level 14. I strongly recommend you watch levels 13 and 14 before continuing with this class. Level 13 is the intro to action queries and covers update queries. Level 14 continues with update queries and teaches append queries, which we'll be using today for the student attendance portion.

This class was recorded with Microsoft Access 2013. It should work fine with 2007 and 2010. The action queries themselves work just fine in pretty much any version of Access, but today I'm going to show you how to edit embedded macros that were new in 2007. If you're using Access 2003 or earlier, I recommend you get my Access 222 class, which covers all of these action queries in detail for Access 2003.

My courses are broken up into beginner, expert, advanced, and developer level classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the expert level classes, which you're in now. When you finish all of the expert level classes, the advanced classes will cover event programming in macros, and the developer classes will cover Visual Basic for Applications.

Each group of classes is broken down into multiple levels, level 1, 2, 3, and so on. In addition to my normal Access classes, I also have seminars designed to teach specific topics.

Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more.

You can find details on all of these seminars and more on the website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you're watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video. Here you will see all of the questions that other students have asked as well as my responses to them and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion.

If you are not watching these lessons on my website, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step. Don't try to apply what you're learning right now to other projects until you've mastered the sample database from class. If you get stuck or don't understand something, watch the video again from the beginning or tell me what's wrong in the student forum and I'll do my best to help you.

Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you'll see that it's real easy to use.

Now, I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at accesslearningzone.com/databases. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database.

One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes with Microsoft Access. You'll find there's a sample database for each of my courses on my website.

Now let's take a few minutes and go over exactly what we're going to cover in today's class.

In lesson one, we're going to begin setting up tracking student attendance in classes. We're going to create a class type table, an individual class table, we'll assign customers and students into these classes, and then set up a many-to-many relationship to track each student in each class.

In lesson two, we'll set up the attendance table with the attendance form and subform. We'll create an append query so we can run it and automatically add all of the students for the currently selected class to the attendance table.

In lesson three, we're going to finish up with student attendance. We're going to make a button to automatically add all of the students to the attendance form for the day, and we'll make a couple of attendance reports: student attendance by date and student attendance by student.

In lesson four, we're going to archive old contacts from our customers by running an append query to copy all of the contacts that are more than a year old into a different table. Then we're going to learn how to use a delete query to delete those records from the original contact table.

Quiz

Q1. What type of relationship is used to track customers and their enrollment in multiple classes in the example discussed?
A. Many-to-many relationship
B. One-to-one relationship
C. One-to-many relationship
D. Self-referencing relationship

Q2. Why do we use an append query when tracking student attendance for each session?
A. To add all registered students to the session automatically
B. To delete all students from previous sessions
C. To update class titles
D. To summarize attendance statistics

Q3. What is the purpose of turning off warning messages when running action queries from a button macro?
A. To prevent unnecessary prompts for the instructor
B. To stop the query from running twice
C. To prevent changes to the database
D. To create a backup before running the query

Q4. Which of the following reports will be created to analyze attendance information?
A. Attendance by date and attendance by student
B. Attendance by class and attendance by teacher
C. Only an overall attendance summary
D. Financial reports for each class

Q5. What is the process described for archiving old contacts in the customer database?
A. Use an append query to copy old records to a new table, then use a delete query to remove them from the original table
B. Manually delete old records one by one
C. Export old records to Excel only
D. Automatically overwrite old records

Q6. What prerequisite is strongly recommended before taking Access Expert Level 15?
A. Watching levels 13 and 14
B. Taking the Developer classes first
C. Reading the Northwind Traders documentation
D. Having experience in SQL Server

Q7. In which versions of Access should the action queries and demonstrated techniques work properly?
A. Access 2007, 2010, 2013, and newer
B. Only in Access 2013
C. Access 95 and earlier
D. Only in Access Online

Q8. What should a student do first when learning from the video lessons to get the most out of the course?
A. Watch the lesson through completely without trying to do anything
B. Try to apply the concepts immediately to their current business
C. Only read the textbook
D. Skip to the advanced topics

Q9. What should a student do if they get stuck during the lesson?
A. Watch the video again or ask for help in the student forum
B. Stop the course and try another one
C. Ignore the problem and move on
D. Contact Microsoft support

Q10. Where can students find sample completed databases for each course level?
A. On accesslearningzone.com/databases
B. Only in the printed textbook
C. At Microsoft's official site
D. Through email request only

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 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 video from Access Learning Zone continues our exploration of action queries in Microsoft Access. I am Richard Rost, your instructor, and in this lesson, we are going further with append queries and taking a thorough look at delete queries.

One of the most common questions I receive is how to track attendance. Whether you need to keep records of students in classes or employees in meetings, the methods in this class apply to any scenario that requires you to monitor attendance.

For our example, we will use our fictional PC resale company and add a computer training division. The goal is to record which customers attend different classes. We'll implement a many-to-many relationship, allowing our customers to enroll in multiple classes, and for any given class to have multiple customers. Each customer, which might represent a company, can also send several employees for training.

We will build a form to manage attendance for every customer in each training session. By using an append query, we can quickly take all registered students and place them into that day's class session. This way, the instructor just has to check whether someone is present or absent, rather than typing in all the names each time.

We will also cover how to modify a button macro so that running the append query is a simple one-click process for the instructor. All students are automatically placed into the class for the session, and there are no unnecessary warning prompts disrupting the workflow.

For reporting, we will design two print-friendly reports. The first one shows attendance by date, making it easy to track which students missed particular classes, while the second presents attendance by student, so you can see which days each student was absent.

After that, our focus shifts to delete queries. We will use these to remove old records from your tables automatically. Our example involves archiving outdated contact details from the customer database. Over time, you may accumulate notes or call logs that you do not want to keep in your main database but wish to store elsewhere. First, we copy those old records to a new archive table using an append query, then we delete them from the original table with a delete query.

This class builds on what was taught in Access Expert Levels 13 and 14. It is important to be familiar with those topics before proceeding. In Level 13, you learn the basics of action queries and work with update queries. Level 14 continues with update queries and takes you through append queries, which we will use extensively for tracking student attendance in this class.

The lessons in this course were recorded using Microsoft Access 2013, but everything should work in Access 2007 and 2010 as well. The action queries themselves are supported in nearly every Access version. Macros, as shown here, were introduced in Access 2007. If you are working with Access 2003 or earlier, I recommend looking at my Access 222 class, where I cover these topics in depth for that version.

My courses are structured for different skills levels. Beginners should have completed the beginner lessons before moving to the expert-level material we are covering now. Once you finish all expert classes, the advanced series covers macro event programming and the developer track covers VBA.

Each skill category, such as beginner, expert, advanced, and developer, is split into several levels, and there are also seminars focused on specific tasks. My seminars include topics like building web apps with Access, designing calendars, enhancing database security, adding images and attachments, managing service businesses, doing accounts payable tracking, learning SQL, making loan amortization schedules, and more. You can find information about all seminars and courses on my website.

If you have questions during the lessons, I encourage you to visit the student forums. Those watching the course online on my website will see a forum beside the class video where you can read and respond to questions from fellow students and see my replies. The forums are helpful for discussing the lesson material and clarifying anything you do not understand. If you are not watching these lessons on my website, you can always visit accesslearningzone.com/forums later.

To benefit the most from this course, I recommend that you first watch each lesson all the way through without trying to follow along on your own computer. Once you have seen the lesson and understand what is covered, play it a second time and build the database step by step alongside me. Work through the sample database as shown in the videos before trying to adapt what you have learned to other projects. If you run into trouble, watch the video again or post your questions on the student forum and I will help you.

Remember to keep an open mind. Access can be a little overwhelming when you start out, but as you get more familiar with it, you will see how straightforward it actually is.

I always recommend that you recreate the databases alongside me during the videos, but if you want, you can also download a sample of the completed database from accesslearningzone.com/databases. Sometimes, the easiest way to learn is to take apart someone else's database to see how it works. That is how I learned Access years ago, by working with the Northwind Traders sample database that comes with Access. There is a sample database for each of my classes on my website.

Here is a brief outline of what we will cover in each lesson of this course:

In lesson one, we start by setting up tables for tracking student attendance in classes. We will create a class type table, a table for individual classes, register customers and students into classes, and establish a many-to-many relationship so that each student can be connected to multiple classes.

Lesson two is focused on building the attendance table along with its form and subform. We set up an append query that lets us quickly add all students for a given class session into the attendance table.

In lesson three, we finalize the student attendance tracking system. This involves creating a button that automatically loads all students into the attendance form for the day and producing attendance reports by date and by student.

Lesson four wraps things up by archiving old contact records. We will use an append query to move records more than a year old into a separate table, and then use a delete query to remove them from the original contacts table.

For a full, step-by-step video tutorial that covers all the material discussed here, you can visit my website using the link below. Live long and prosper, my friends.

Topic List

Tracking student and employee attendance
Setting up tables for class types and classes
Assigning customers and students to classes
Creating many-to-many relationships for class attendance
Building an attendance table
Designing an attendance form with subform
Creating an append query for class session attendance
Automating append queries with button macros
Suppressing warning messages in action queries
Generating attendance reports by date
Generating attendance reports by student
Archiving old contact records with append queries
Deleting old records using delete queries

Article

Welcome! In this tutorial, we will take an expert-level look at working with action queries in Microsoft Access, focusing on using append and delete queries to manage data more efficiently. Our main example will be tracking attendance for classes and managing old customer contact records. The techniques here can be applied to many attendance scenarios, such as students in classes or employees in meetings, and are useful for managing transactional information that changes over time.

Let us start by discussing how to track customers participating in classes, which is also applicable for employee training or similar situations. Imagine you run a company offering computer training. Each customer could be a company, and each company can send multiple employees to different classes. Each customer can sign up for multiple classes, and each class can have multiple customers attending, potentially with several students from each. In database design, this is a classic many-to-many relationship.

To set this up, begin by creating three main tables in your Access database:

First, make a table for all your customers. Each customer record represents a client company. Give each customer an ID field, and include fields for the company name and contact details.

Next, create a table for your classes. This table will list each specific instance of a training class, with its subject (like Excel or Access), start date, and other relevant details. Again, add a unique class ID for each class.

Then, create a table for the students. In this setup, a student might be an individual employee from a customer. This table links each student to a customer and stores the student's name and perhaps contact information.

The many-to-many relationship will be managed with a junction table that connects students and classes. Create a table, for example called ClassRegistration, where you store the StudentID and ClassID. Each record means a particular student is registered for a specific class. This allows you to quickly find which students are in each class and which classes a student is attending.

To track attendance for each class session, introduce another table called Attendance. Each attendance record should include: AttendanceID (an autonumber to ensure each record is unique), the StudentID of the attendee, the ClassSessionID (if your classes have multiple sessions, like different days), the Date, and a Present field (Yes/No) to mark whether the student attended. This way, you can record, for each scheduled class date, who was present or absent.

Now, to save time for the instructor, you can automate the process of adding enrolled students to the attendance table for a given day. Instead of manually entering each student's name for every class session, use an append query in Access. An append query allows you to select all the students registered for a class and automatically add a record for them in the Attendance table for a particular session. To do this, create a query that selects students from the ClassRegistration table for the selected ClassID and inserts a record with the attendance date and default attendance status (perhaps defaulting to Present = False until confirmed).

To make this process even smoother, create a button on your class attendance form. When the instructor clicks the button, the append query runs and adds the necessary attendance records for all students who are supposed to be at that class session. You can use a macro or VBA event to automate this. For example, in the button's OnClick event, you could run code like this:

DoCmd.SetWarnings False
DoCmd.OpenQuery "AppendAttendanceRecords"
DoCmd.SetWarnings True

This code suppresses warning messages, runs the append query, and then restores the warnings. It ensures that the instructor is not bothered by unnecessary prompts while entering attendance.

After tracking your attendance, it is useful to produce printed reports. Two helpful reports are attendance by date and attendance by student. The attendance by date report shows, for each class session, who was present or absent, letting you see which students missed which classes. The attendance by student report lists each student along with their absence record, so you can see trends or patterns for each attendee over time. You can build these reports as grouped and sorted Access reports using simple queries on the Attendance table.

Now, let us shift to learning how to manage old contact information using append and delete queries. Suppose your customer database holds years of contact logs, notes, or interactions. Over time, this bulk of historical data may slow down your database or make relevant records harder to find. Instead of deleting old records outright, you might want to archive information that is no longer needed for daily operations while keeping your main tables focused on current data.

Start by creating a new table for archived contacts. This table should have the same structure as your contact table, so that archived data fits perfectly. To move old records, first run an append query that selects contacts older than a specified date (for example, over a year old) and copies them to the archive table. Your append query might look like:

INSERT INTO ContactArchive (ContactID, CustomerID, ContactDate, Details)
SELECT ContactID, CustomerID, ContactDate, Details
FROM Contact
WHERE ContactDate < DateAdd("yyyy", -1, Date())

This query copies all contacts older than one year from the main Contact table to your ContactArchive table. Once the records have been safely copied, you can then use a delete query to remove them from the original table. The delete query could be:

DELETE FROM Contact
WHERE ContactDate < DateAdd("yyyy", -1, Date())

This sequence safely archives your old data and keeps your working tables efficient.

As you work with action queries, take care and always back up your database before running queries that alter your data. Action queries, especially delete queries, cannot be undone, so be cautious.

If you want to follow along with these examples, it is best to focus on creating the same sample database described here before applying the techniques to other projects. Doing it step by step lets you build confidence and reduces mistakes. If something does not work as expected, review your table structures and relationships, and re-run the queries.

In summary, using append and delete queries in Access gives you powerful tools to automate data entry and maintain your database efficiently. Whether you need to track class attendance, manage meeting records, or archive old entries, these techniques make managing large and changing datasets much easier. With practice, you will find they are straightforward to use and can save you significant time and effort in your daily Access work.