Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Advanced Level 4 and Level 5   dismiss
 
 

< Previous: Access Expert 14

Next: Access Expert 16 >

Access Expert Level 15

Expert Microsoft Access Tutorial - 1 Hour, 57 Minutes
 
 
This Microsoft Access video tutorial picks up where Expert Level 14 left off. In this class we will continue to learn how to use Append Queries to add data to tables, and Delete Queries to delete records from a table. Topics include:
 
  - Track Student Attendance
  - Put Customers / Students in Classes
  - Create a Daily Attendance Form
  - Use a Macro to Run Append Query
  - Disable System Warnings
  - Enable All Macro Actions
  - Attendance Reports by Student / Date
  - Delete Queries
  -
Archive Old Customer Contacts

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.




 

Access Expert Level 15
Description: Access Expert Level 15
Versions: Recorded with Access 2013. Also use with 2007 and 2010. The lessons on embedded macros are valid for Access 2007 and later only. Access 2003 users should get Access 222.
Pre-Requisites: Access Expert Level 14 strongly recommended
Running Time: 1 Hour, 57 Minutes
Cost: $24.99


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)

 


 
Keywords: Append, Delete Queries, Student Attendance, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, students, classes, cdate function, modal, popup, left join, setwarnings, show all actions, requery
 
 

Student Interaction: Microsoft Access Expert 15

Richard on 10/7/2013:  Microsoft Access Expert Level 15 is 1 hour, 57 minutes long. This tutorial will continue our focus on Append Queries to add data to tables, and we'll begin learning about how to use Delete Queries to delete records from tables. We will learn how to track student attendance in classes, and archive old contacts into a history table. Topics include: - Track Student Attendance - Put Customers / Students in Classes - Create a Daily Attendance Form - Use a Macro to Run Append Query - Disable System Warnings - Enable All Macro Actions - Attendance Reports by Student / Date - Delete Queries - Archive Old Customer Contacts Click here for more information on Access Expert Level 15, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 14. The next class in the series is Access Expert 16.
Alex Hedley on 10/9/2013: You do make me laugh when you goof,
I tried shouting but I don't think you heard :p

Reply from Richard Rost:

LOL

Eleanor Mason on 10/9/2013: At the 12:16 mark when I run my query mine is blank not showing any records. I have watched the segment 4 times. I had set my foreign key to a number so I didn't have that problem. I did my joins. However for some reason my query is blank.
Elaine Mason on 10/10/2013: Disregard I figured it out

James Gray on 10/14/2013: Rick,

Basing the attendance form on AttendanceTodayQ would reset by default all the Present boxes to true, wouldn't it? That would erase all the actual attendance from the first time it was taken, correct?

James Gray on 10/15/2013: Rick,

My last comment about defaulting to Present yes assumed that attendance was taken before appending to table. After looking at the next lesson, I see that you append the records to the table first so that changes to the Present field are not defaulted again. My bad and sorry for wasting your time.

Reply from Richard Rost:

No problem. It's good you figured it out for yourself.

fred on 10/28/2013: Is it posible to apply these excel formula to MS Access? because i want to get the difference between two dates like RECEIVED DATE and how many days DELAYED on the text box

=IF(OR(U40=0,U40="")=TRUE,0,U40+14)
and
=IF((X40=0)=TRUE,0,IF(Y40>0,IF(Y40-X40<=0,0,Y40-X40),TODAY()-X40))

Bea Anderson on 10/29/2013: We have a 3 day training of around 100 students with up to 3 classes per student per day. I need to create a letter for the student showing all the classes she/he signed up for. I created the letter from a query, but it only shows one class. How do I get it to list all the classes for that student.
Alex Hedley on 11/3/2013: You can use the IIF Function.

TaxRate: IIF(State="NY", 0.875, 0)

You can use the Date Diff Function to calculate the difference between dates.

Alex

Alex Hedley on 11/3/2013: Hi Bea,

Does the Query show the correct number of records per student?
Do you have show each record on new page?
Does the report have multiple pages that you can scroll through?

You may need to look into Sorting and Grouping in Reports:

1. How to use a Sorting & Grouping Level to group by Year.
2. Using Sorting & Grouping Levels in Access 2010.
3. Learn how to use Sorting & Grouping Levels in your Access Reports.

Alex

Bea A on 11/11/2013: I tried to do grouping, but it repeated the letter. It suddenly occurred to me after reading your reply that if I put the other information in the group header and footer, it wouldn't repeat. It worked great. Thanks.
Bea Anderson on 11/12/2013: Now that I have the letter working I need to have each letter on a different page. How do I do that.

Reply from Richard Rost:

FORCE NEW PAGE after the group footer.

Bea Anderson on 11/12/2013: Thanks, I tried that once and it forced a page after each class in the group, but it worked this time.
William Beeler on 12/9/2013: Rick:

I have been trying to get this set up for a while now. Each time, all the names from the previous classes show up. In other words, if I add people from a class with 10 people in it, and then go to take attendance in a class with four people in it, the append query throws all 14 names into the form. What am I doing wrong?

William

Wayne Ayotte on 12/10/2013: I recreated the attendance lesson to reflect boy scout nmaes(Company Name) attending troop meetings. I tried to figure a way of selecting all the scouts so the user doesn't to select the scout from the combo box in meeting form (Class Form)one at a time and than go the attendance form. I haven't had any success. Could point me in the right direction? I hope this is clear to you what I'm trying to do.
William B on 12/10/2013: Rick:

I got it figured out. I didn't set my ClassID to the current form, and that is why I was getting every person for that date in my subform.

Lynda Chase on 8/18/2014: Hi Richard

It seems when I make the attendanceF modal and popup, it causes access to freeze up. so when I go to classF and click on the attendance button, a bell sounds, and then I have to shut down access and reopen. This also happened with an earlier class where modal and pop up were switched on. If I turn them off, it all functions perfectly - is this a glitch with my access, or with me? :o)

Reply from Richard Rost:

I've NEVER heard of this happening before. Make sure you have the latest bug fixes for Windows AND Office (run a Windows Update and an Office Update).

David on 9/28/2014: can i have the tutorial video of this access 2013 expert 15? the project that my prof gave me is exactly like this please help me. may completion due is nearer. hoping for your help.

Reply from Rick Rost:

Yes, you can purchase Access 2013 Expert 15 right here: ORDER.

Frank Fish on 12/9/2014: Richard, I created copies of the tables as explained in the lesson.(CopyOfContactsT; ArchiveCopyOfContactsT)I created a query that would select all records in the "Active" field (true/false)that were not active (false). when I ran the query it showed the (7)records. I then followed your instructions to append those records to the archive table; when I ran it I got this error: "SELECT* cannot be used in an INSERT INTO query when the source or destination table contains a multi-valued field."

Reply from Alex Hedley:

I remember a Field being changed in this series as it's not advised to use them, this maybe the case here.

GeorGE jOHNSON on 4/3/2015: I'm looking to upload my database to Sharepoint where it can be used by other staff. Which course is best to teach me how to do this?

Reply from Alex Hedley:

There hasn't been a course yet but you can vote for it on the Waiting List

It was mentioned briefly in Expert 20.
Search Sharepoint

vicki Hudson on 4/15/2015: Where did I go wrong? When I change the date in the ClassF to a date I know I have no Students then click on Attendance button, In my AttendanceF the Student names are all there and the same "Present" boxes checked.

Reply from Alex Hedley:

Is your Date Criteria and =, BETWEEN, >, <, <= or >=
If you run the query manually what does it show?

vicki H on 4/16/2015: I changed the DateTime default value in AttendanceT to =Date() as opposed to =Now() and it asked to update 2 queries and I said yes and that fixed it. Your just awesome Alex! Made me think about it :-)

Reply from Alex Hedley:

Happy to help :)
Dates with Times can be a real pain!

Katheryn Hartig on 3/28/2016: The CustomerID field in the ClassXCustomerT technically should be type Number. I get that in this example it doesn't really matter as long as you only put numbers in the field but just thought I'd let you know.

Reply from Alex Hedley:

Good spot, not sure why this is text.

Tim Florio on 4/8/2016: Greetings, Expert 15, Lesson 1, ClasXCustomerSubF, CustomerID Combo Box. I followed the steps in the video when creating the combo box, deleted it two times, remade it three times and in fact went back through the entire lesson to double check everything looking for a mistake. The issue; the subject combo displays the CustomerID instead of NewName. If I hit the drop down arrow it displays the NewName but when I click on it only the CustomerID is displayed again. I hid the CustomerID column when built the combo box so that's even more confusing. Everything on both forms appear to work with the exception of the subject combo box.

Reply from Alex Hedley:

Did you change the bound column?
I take it Column Widths is 0;x;

Michael Larsen on 4/19/2016: Hi Richard

I am sorry to have to tell you that this class, in my view, appears messy.
You change your mind several times and it makes it hard to follow your train of thought.
Please make a plan ahead of recording and stick to it.

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP