Access 2007-2019
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  

News      User Comments     History     Notify Me

10/7/2013 3:59:22 PM
Microsoft Access Expert 15
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

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.

Permanent Link
Course Link: Microsoft Access Expert 15
Keywords: access expert 15 append query delete query student attendance archive contacts
Page Tag: whatsnew
Post Reply

Greetings Expert 15 Lesson 1 ClasXCustomerSubF Comment from Tim Florio @ 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;
Show Just This Thread        Post Reply
The CustomerID field in the ClassXCustomerT Comment from Katheryn Hartig @ 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.
Show Just This Thread        Post Reply
Date Choice Comment from vicki H @ 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!
Show Just This Thread        Post Reply
Date Choice Comment from vicki Hudson @ 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?
Show Just This Thread        Post Reply
Sharepoint Comment from GeorGE jOHNSON @ 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
Show Just This Thread        Post Reply
Multi-Valued Field Comment from Frank Fish @ 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.
Show Just This Thread        Post Reply
can i have the tutorial video of this access 2013 Comment from David @ 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.
Show Just This Thread        Post Reply
modal popup lockup Comment from Lynda Chase @ 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).
Show Just This Thread        Post Reply
Microsoft Access Expert 15 Comment from William B @ 12/10/2013

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.
Show Just This Thread        Post Reply
Comment from Wayne Ayotte @ 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.
Show Just This Thread        Post Reply
attendance Comment from William Beeler @ 12/9/2013

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?

Show Just This Thread        Post Reply
Training Class Letter Comment from Bea Anderson @ 11/12/2013
Thanks, I tried that once and it forced a page after each class in the group, but it worked this time.
Show Just This Thread        Post Reply
Training Class Letter Comment from Bea Anderson @ 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.
Show Just This Thread        Post Reply
Training Class Letter Comment from Bea A @ 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.
Show Just This Thread        Post Reply
Comment from Alex Hedley @ 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.

Show Just This Thread        Post Reply
Microsoft Access Expert 15 Comment from Alex Hedley @ 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.

Show Just This Thread        Post Reply
Comment from Bea Anderson @ 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.
Show Just This Thread        Post Reply
Microsoft Access Expert 15 Comment from fred @ 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

Show Just This Thread        Post Reply
Default Comment from James Gray @ 10/15/2013

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.
Show Just This Thread        Post Reply
AttendanceT Comment from James Gray @ 10/14/2013

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?
Show Just This Thread        Post Reply
my query mine is blank Comment from Eleanor Mason @ 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.
Show Just This Thread        Post Reply
You do make me laugh when you goof I tried shout Comment from Alex Hedley @ 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:

Show Just This Thread        Post Reply

Add Your Comments or Subscribe
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 2+6:
  Notify me when the News is updated.
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



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

8/15/2019Last Chance for Access CDs
8/1/2019Customer List Form Template
8/1/2019New Template Access Customer Database
7/30/2019Microsoft Access Developer 13
6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
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