|
This class
picks up where
Expert Level 14
left off. In Level 14 we started learning about Append Queries. In
today's class we will learn two more great examples of when to use
Append Queries. First, we'll begin with a very popular request:
tracking student attendance. We'll set up a list of classes, and put
students into those classes.
Next, we'll create the student attendance
table and a form for entering daily attendance. Then, we'll
create an Append Query that will take all of the students and add
them to the current day's attendance automatically. We'll put a command
button with an embedded macro on the attendance form to run this
query, turning off all system warnings (temporarily) and then requery
the form results. Now, all the instructor has to do is check the box
indicating which students are absent.
We will create a button on the Class form
so that the instructor can take attendance for ANY date. This way
if he forgot to take attendance yesterday, or perhaps wrote it down on
paper, he can go back and enter results from other days. We'll also set
up a couple of attendance reports.
The first report shows each class,
grouped by DATE, sorted by student. This allows you to easily see
who was absent from each class on each date.
The next report is similar, but is
grouped by STUDENT, so you can enter a range of dates and see how
many days that student was absent in that date range.
Next we'll learn about Delete Queries.
Use can use Delete Queries to remove data (delete records) from a table.
They're very dangerous so make sure to back up your tables first. We
will create an Append Query to copy old customer contacts from the
Contact Table to a Contact Archive Table, and then we'll Delete them
from the Contact Table.
This is the 15th class in the Access Expert series.
There's a lot of great material in this class. Learning Action Queries will add tremendous power to your databases. Of
course, if you have any questions about whether or not this class is
for you, please contact me.
Complete Outline - Access Expert Level
15
00. Introduction (8:27)
01. Student Attendance 1 (20:12)
Types of Different Classes
ClassTypeT
Individual Class Sessions
ClassT
ClassQ with Type Name
Track Customers in Each Class
ClassXCustomerT
Class Form with Enrollment Subform
ClassF
ClassXCustomerSubF
Add Multiple Students to Class
Each Company Sends Multiple Students
02. Student Attendance 2 (31:12)
Create Attendance Table
AttendanceT
Master Attendance Form
Student Subform
Modal Popup
Default Values from Other Forms
AttendanceQ With Supporting Data
Left Joins
Create Attendance Subform
Data Entry Property
Attendance Append Query
Add Static Date Value to Append Query
CDate Function |
03. Student Attendance 3 (34:59)
Create Button to Run Append Query
SetWarnings OFF
Turn on Potentially Unsafe Actions
Show All Actions
Requery
Short Date Format for Date Picker
Show Attendance for Any Date
Attendance Report By Class By Date
Sorting & Grouping
Attendance Report by Student then Date
04. Contact Archive (15:37)
Backup Contact Table
Create Contact Archive Table
ContactArchiveAppendQ
Remove Indexing Primary Key from Archive
Change AutoNumbers to Numbers
05. Review (6:31) |
|
