Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Comments for Access Expert 6

Age	Subject	From
5 months	Cannot Join, Help	Rudolpho Galicia
6 months	601 Reserved Words	Michael Olgren
2 years	Page numbers on reports	Jim Kozlovich
2 years	Multiple Orders on same letter	Barbara Learned
2 years	Access Level 6 Lesson 3	Kenneth Wykoff
2 years	Access Expert Level 6 Lesson 1	Kenneth Wykoff
2 years	Overlapping Views	Ronald Bella
2 years	DateTime	Chistine Everett
2 years	E605 1048	Benjamin Heacox
3 years	Print Letters to PDF	Dan Roehm
3 years	Page Breaks	Patricia Tonge
3 years	My printer not set up I think	Gregory Clancey
3 years	Collection Letters Three	George Wilson
4 years	Error box	Richard Van Wagoner
4 years	Access Expert 6	Paul Kehyee
4 years	Suggestion	Maggie M
4 years	Days Ago as Number	Abdulhadi Qasem
5 years	number of days	Mickael Sorensen
5 years	Type Error	Larry Large
5 years	Copy and Paste	Larry Large
Next >>

Subscribe to Access Expert 6
Get notifications when this page is updated

Intro

In lesson 6 of Microsoft Access Expert, we will build on the previous class by learning how to send collection letters to customers with past due orders, set up order tables, and create queries to identify which customers need these letters. We will customize and manage multiple letter templates based on the age of outstanding orders, automate date ranges for different collection letters, and use fields such as active status and lead source to broaden our mass mailing capabilities. You will also see how to generate letters for all active customers or those in specific categories.

Transcript

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

In today's class, we're going to continue with the theme of Expert 5, the last class, by learning how to send more types of letters directly from our Access database. In today's class, we're going to send collection letters to our customers, so we'll set up an order table, put some orders in the system, and then we'll send collection letters to all the customers who have orders that are past due.

We'll set up multiple letter templates in our system. This way, customers who have orders less than 30 days old get a nice letter, customers with orders from 30 to 60 days old get another letter, and then customers who have orders more than 60 days old get an SD collection letter. We'll set a date range for each collection letter. This way, when you pick your 30 to 60 day collection letter, for example, the specific date criteria are set on the form for you, and that determines which customers get their collection letters. It's all done automatically.

Next, we're going to broaden the letter writing form so that we're not limited to just sending collection letters. We're going to utilize that is active field that we added to our customer table way back in lesson one, so we can send a letter to all of the active customers in our database.

Then, finally, we'll utilize the lead source category that we put on our customer form, so we can send a letter just to customers in a particular category or lead source. I show you several different ways to generate mass mailings. You can take these tools and set up any kind of criteria that you want.

Now, this class is based heavily on the techniques we covered in the previous class, Access Expert Level 5. If you have not watched Expert Level 5 first, I strongly recommend you watch it before watching this class. This class is almost a part 2 to the Level 5 class.

I will be using Access 2013. However, everything that I cover in today's class should work just fine with 2007 and 2010. If you're using either of these older versions and you come across something that doesn't work, please let me know.

If you're using Access 2003 or earlier, you should either upgrade to Access 2013, which is what I recommend, or visit my website at AccessLearningZone.com and look for my Access 205 Tutorial that covers roughly the same material that this class covers.

You are strongly encouraged to build the database that I build in class. It's the best way to learn. However, if you're lazy like me, go ahead and download it from my website at AccessLearningZone.com/databases.

My courses are broken up into four groups: Beginner, Expert, Advanced, and Developer. The Beginner lessons are designed to give you a basic overview of the simple features of Access. The Expert series, the one you're in now, is designed for more experienced users who are already comfortable with Access. The Advanced lessons cover working with macros, automation, and many more advanced features. The Developer lessons get into programming with Visual Basic for Microsoft Access.

Each of the series is broken down into different numbered levels, starting with Level 1. The Beginner series, for example, had Levels 1 through 9.

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, tracking accounts payable, learning the SQL programming language, and lots more. You can find complete details on all these seminars and more on my 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 using my custom video player software or the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class videos, as long as you have an active internet connection. 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 may have made. I encourage you to read through these questions and answers as you start each lesson, and feel free to post your own questions and comments as well.

If you're not watching the lessons online, you can still visit the student forums later by visiting AccessLearningZone.com/forums.

To get the most out of this course, I recommend that 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 master the sample database from this 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. Most importantly, keep an open mind. Access might seem intimidating at first, but once you get the hang of it, you'll see that it's really easy to use.

Now let's take a closer look at exactly what's covered in today's class. We're going to begin by sending collection letters to our customers who have orders that are past due and unpaid.

In lesson 1, we're going to build an order table, and we're going to set up a query so we can see all of the customers that need to get collection letters.

In lesson 2, we'll take the query that we made in lesson 1 and use it to build a report with a customized message to the customer.

In lesson 3, we're going to make our collection letters customizable. We're going to set up a letter table where we can have three separate letters: one for customers who are less than 30 days old, one for customers who are 60 days old, one for customers who are more than 60 days old, and so on. We'll be able to specify the beginning and ending dates and generate a custom letter for each of our groups of customers.

In lesson 4, we'll see how to change the range of dates based on the collection letter that you pick. If you pick the less than 30 days collection letter, for example, the date range changes automatically from 30 days ago to today.

In lesson 5, we're going to broaden the scope of our mass letter writer. We're going to do more than just collection letters. We're going to generate letters for all of our active customers. We're also going to generate letters for customers based on their lead source or category.

Thank you.

Quiz

Q1. What is the main purpose of Expert Level 6 in the Microsoft Access course series?
A. Learning how to create new databases from scratch
B. Sending various types of letters, especially collection letters, directly from Access
C. Designing websites using Access
D. Creating advanced charts and graphs

Q2. What new type of table is built in Lesson 1 of this class?
A. Customer table
B. Product table
C. Order table
D. Employee table

Q3. How does the course suggest differentiating collection letters for customers?
A. By the size of their last order
B. By the date range of their outstanding orders
C. By the customer's address
D. By the number of orders placed

Q4. What is the benefit of setting up multiple letter templates in the system?
A. To randomly select which letter to send to customers
B. To provide generic messages to all customers
C. To send specific letters based on the overdue duration of an order
D. To automate order entry

Q5. Which field is used to send letters to active customers only?
A. Order Date
B. Customer Name
C. Is Active field
D. Product Category

Q6. How does the course expand the letter writing functionality beyond just collection letters?
A. By allowing emails to be sent instead
B. By allowing letters to all active customers and categorizing by lead source
C. By exporting letters to Excel
D. By printing only invoices

Q7. If someone is using Access 2003 or earlier, what does the instructor recommend?
A. Continue with Expert Level 6 as is
B. Upgrade to Access 2013 or check the Access 205 Tutorial
C. Skip collection letters
D. Only use the Beginner series

Q8. What is the best way to learn according to the instructor?
A. Reading through the entire manual first
B. Watching one lesson and applying it to unrelated projects
C. Building the database step by step along with the video lesson
D. Relying solely on seminar notes

Q9. What series are the Microsoft Access courses divided into?
A. Expert, Professional, User, Admin
B. Beginner, Expert, Advanced, Developer
C. Starter, Intermediate, Professional, Master
D. Simple, Hard, Professional, Advanced

Q10. Where can students ask questions and participate in course discussions?
A. By sending emails only
B. In student forums on AccessLearningZone.com
C. On social media only
D. By calling customer support

Q11. What is one of the first steps in generating collection letters as described in the class?
A. Building a shipping label table
B. Setting up a query to find customers with past due orders
C. Creating workflow macros
D. Designing a marketing report

Q12. What does lesson 3 focus on regarding collection letters?
A. Printing shipping labels
B. Customizing letter content based on customer order age
C. Deleting inactive customers
D. Filtering customers by city

Q13. What is the recommended approach if you do not understand a lesson or get stuck?
A. Skip to the next lesson
B. Watch the video again or post on the student forum
C. Guess the correct steps
D. Read the course manual

Q14. What does lesson 5 demonstrate in this course?
A. Creating printable invoices
B. Generating letters for active customers and those filtered by lead source or category
C. Writing SQL from scratch
D. Importing data from Excel

Q15. How are the lessons in each course series organized?
A. In alphabetical order
B. As random unnumbered topics
C. In numbered levels starting from Level 1
D. Only available as one long video

Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-B; 7-B; 8-C; 9-B; 10-B; 11-B; 12-B; 13-B; 14-B; 15-C

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 is Microsoft Access Expert Level 6. My name is Richard Rost, and I will be guiding you through today's lesson.

In this course, we continue where Expert Level 5 left off, expanding our capabilities to send different types of letters directly out of the Access database. We'll focus on creating and sending collection letters to customers with overdue orders. To accomplish this, we will build an order table, input some sample orders, and identify customers whose orders are past due so our system can send each of them an appropriate collection letter.

Our system will support multiple templates for collection letters. For example, customers with orders less than 30 days overdue will receive a more gentle reminder, while those with orders overdue 30 to 60 days will get a more assertive note. Orders overdue by more than 60 days will trigger an even firmer letter. We'll set a date range for each template, so when a particular letter is selected, the date criteria are already in place, and Access will know exactly which customers should receive which letter. This entire process is designed to be automatic.

We will also improve the letter-writing form so it's not restricted to just collection letters. We'll use the 'is active' field from our customer table, which we created back in the first lesson. This allows us to easily send letters to all active customers in the database.

In addition, we will use the lead source category from our customer form. This feature will enable us to send targeted letters to customers in specific categories or from particular lead sources. I will show you several ways to generate large mailings using these methods, and you will learn to set up and use whatever criteria suit your needs.

This class builds directly on the concepts taught in Access Expert Level 5, so I strongly recommend you go through that previous class before starting this one, as this session is really a continuation of those lessons.

I will be working in Access 2013, but the material covered applies to both Access 2007 and 2010 as well. If you encounter any compatibility issues with these earlier versions, please let me know. For those still using Access 2003 or even older versions, I suggest upgrading to 2013, or you can consult my Access 205 tutorial for material similar to what I am teaching today.

I always recommend that you build the database along with me. This hands-on approach is the most effective way to learn. Still, if you prefer, you can download a copy of the database from my website.

I organize my courses into four levels: Beginner, Expert, Advanced, and Developer. The Beginner series covers the basics of Access. The Expert series, such as this course, is intended for those who are already comfortable with Access and want to learn more advanced topics. Advanced lessons take you through features like macros and automation, while Developer lessons focus on programming with Visual Basic for Microsoft Access.

Each series includes several levels. For example, the Beginner series contains nine levels, starting with the absolute foundations.

In addition to these regular Access courses, I offer seminars that focus on special topics, such as building web databases, making calendar-like forms and reports, securing your database, working with images and attachments, writing work orders, handling accounts payable, learning SQL, and many others. More information about all of these is available on my website.

If you have any questions about today's topics, you're welcome to post them in my student forums. If you are using my custom video player or the online theater on my website, the forum for each lesson appears next to the video as long as you have a live internet connection. You can read all previously asked questions and answers for the lesson, as well as my responses. I encourage you to review these discussions when beginning each lesson and add your own questions or comments if you need help or want to participate in the conversation.

If you are not viewing the course online, you can still reach the forums by visiting my website at any time.

To get the most from the course, I suggest first watching each lesson all the way through without stopping so you can get a feel for the entire process. Then watch again and follow along with my instructions, building the database step by step. This approach helps you fully master the techniques before trying to apply them to your own unique projects.

If you run into any problems or do not understand a step, go back and review the video or ask for help in the student forums. Remember, Access can look a bit intimidating in the beginning, but once you gain some experience, you'll realize how intuitive and powerful it really is.

Here is what we'll cover in today's class:

First, we will look at how to send collection letters to customers with unpaid, overdue orders.
In lesson 1, we'll set up an order table and build a query that lists all customers who require collection letters.
In lesson 2, we will use that query to create a customized report that generates a personalized message for each customer.
Lesson 3 focuses on making our collection letters flexible. We'll set up a separate letter table, allowing us to maintain different letter templates for different overdue periods. We'll be able to specify the date range for each group and generate a letter appropriate for each customer group.
Next, in lesson 4, you'll see how the date range for sending letters changes automatically based on which template you select. For instance, choosing the less-than-30-day letter updates the date criteria accordingly.
Finally, in lesson 5, we'll expand our mass letter-writing tool further. In addition to collections, we'll send letters to all active customers and address mailings to customers by lead source or other categories.

For a complete video tutorial with step-by-step instructions covering all these topics, please visit my website using the link below.

Live long and prosper, my friends.

Topic List

Building an order table for customer orders
Creating a query to identify customers needing collection letters
Designing a report to send customized collection letters
Setting up a letter table with multiple letter templates
Customizing collection letters based on order age
Automating date range selection for collection letters
Generating letters for all active customers
Generating letters based on lead source or customer category
Expanding the letter writing form for various mass mailings