Introduction

Welcome! Send Collection Letters & Mailings

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

Welcome to Microsoft Access Expert Level 6. In this course we will build on concepts from Expert Level 5 by learning how to send collection letters to customers with overdue orders using Microsoft Access. We will set up an order table, create customizable letter templates for different date ranges, and automate the process of selecting and sending letters based on specific criteria. We will also discuss how to broaden the letter writing system to send messages to active customers or by lead source category. Throughout, you will see several techniques for generating mass mailings and working with queries and reports.

Navigation

Prev: X06
Next: Lesson 01

Keywords

Access Expert, collection letters, order table, past due customers, letter templates, date range criteria, mass mailings, letter writing form, is active field, lead source category, custom reports, unpaid orders, customer categories, form automation, quer

Subscribe to Introduction
Get notifications when this page is updated

Intro

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 are 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 are going to send collection letters to our customers, so we will set up an order table, put some orders in the system, and then we will send collection letters to all the customers who have orders that are past due.

We will 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 will 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 is all done automatically.

Next, we are going to broaden the letter writing form so that we are not limited to just sending collection letters. We are 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 will 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 are using either of these older versions and you come across something that does not work, please let me know.

If you are 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 is the best way to learn. However, if you are 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 are 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 are 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 are 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 are 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. Do not try to apply what you are learning right now to other projects until you master the sample database from this class.

If you get stuck or do not understand something, watch the video again from the beginning or tell me what is 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 will see that it is really easy to use.

Now let us take a closer look at exactly what is covered in today's class.

We are going to begin by sending collection letters to our customers that have orders that are past due and unpaid.

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

In lesson 2, we will 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 are going to make our collection letters customizable. We are 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 30 to 60 days old, and one for more than 60 days old, and so on, and we will 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 will see how to change the range of dates based on the collection letter that you pick. So if you pick the less than 30 days collection letter, the date range changes automatically from 30 days ago to today.

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

Thank you.

Quiz

Q1. What is the main topic of Microsoft Access Expert Level 6?
A. Sending collection and mass letters directly from Access
B. Creating pivot tables in Access
C. Automating email sending through Outlook
D. Building secure user logins in Access

Q2. What do you need to set up in order to send collection letters to customers in this course?
A. A report with all customer addresses
B. An order table with order dates and unpaid status
C. A macro for sending emails
D. A separate Access database

Q3. How does the class customize collection letters for different customers?
A. By manually editing each letter for every customer
B. By setting up multiple letter templates based on date ranges
C. By using Word Mail Merge only
D. By applying different fonts and colors

Q4. What field added in lesson one is used to target only certain customers with mass mailings?
A. Customer state
B. Customer phone number
C. Is active field
D. Order status

Q5. How does Access determine which customers get a specific collection letter?
A. By customer last name
B. By the size of their last order
C. By the date range set for each collection letter template
D. By customer city

Q6. What tool in Access is used to display a customized message to the customer after building the order query?
A. A macro
B. A table
C. A report
D. A data macro

Q7. Besides collection letters, what other ways does the course teach to generate mass mailings?
A. By employee department
B. By product line
C. By active status or lead source/category
D. By supplier rating

Q8. What is strongly recommended before watching this class?
A. Complete the Advanced Level 10 class
B. Watch Access Expert Level 5 first
C. Install Access 2003
D. Know VBA programming

Q9. If you are using Access 2003 or earlier, what does the instructor recommend?
A. Switching to another database program
B. Watching Access Advanced Level 5
C. Upgrading to Access 2013 or watching the Access 205 Tutorial
D. Looking for a free online course

Q10. What is suggested as the best way to learn from this course?
A. Just read the transcripts
B. Memorize all the video steps
C. Build the database yourself following along with the video
D. Download and use the finished database only

Q11. What should you do if you get stuck on a lesson?
A. Skip to the next lesson
B. Watch the video again or ask for help in the student forum
C. Email your question to Microsoft
D. Contact customer support

Q12. What structure do the AccessLearningZone courses follow?
A. Beginner, Intermediate, Advanced, and Programming
B. Basic, Expert, Professional, and Master
C. Beginner, Expert, Advanced, and Developer
D. Level 1 to Level 20 only

Answers: 1-A; 2-B; 3-B; 4-C; 5-C; 6-C; 7-C; 8-B; 9-C; 10-C; 11-B; 12-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 continues the concepts we covered in Expert Level 5. In this lesson, we will explore how to send out various types of letters directly from your Access database, with a special focus on collection letters for customers with past due orders.

We will start by setting up an order table and entering some orders. Based on these orders, we will create a process that identifies which customers have past due balances and need to receive collection letters.

Our system will include multiple letter templates. This means customers who are overdue by different numbers of days will receive different messages. For example, someone less than 30 days late will get a friendly reminder, those between 30 and 60 days will receive a more serious letter, and those over 60 days will get a more advanced collection notice. Each collection letter template will be associated with its own date range criteria. When you select a specific letter, the date criteria on your form are updated automatically, so Access can determine who receives which letter.

Next, we will make the letter writing feature more flexible so it is not just for collections. We will make use of the "is active" field in our customer table, which we added all the way back in the first lesson, so you can also send letters to all active customers.

In addition, we will use the lead source category field on the customer form, allowing you to send out letters to just the customers from a specific category or lead source. I will demonstrate a few different ways to generate mass mailings using any set of criteria you want.

It is important to note that this class really builds upon the techniques found in the previous lesson, Expert Level 5. If you have not seen that one yet, I strongly recommend you do so first, as this session is essentially a continuation.

The database used here is built in Access 2013, but everything should work well in Access 2007 and 2010. If you run into issues with either of those versions, please let me know. If you are using Access 2003 or an earlier version, I would suggest upgrading to Access 2013 if possible. Alternatively, you can visit my website and look for my Access 205 Tutorial, which covers similar material for those older versions.

As always, I encourage you to follow along with the database I build in the video. Hands-on practice gives you the best learning experience. However, if you would rather not build it yourself, you can download the completed database at my website.

Just to give you some perspective, my Access courses are organized into four groups. The Beginner series offers a basic overview and introduction. Expert lessons, which you are in now, are intended for those who already understand the basics. Advanced lessons start covering topics such as macros and automation. Developer lessons move into Visual Basic programming for Access. Each group is divided into numbered levels, starting with Level 1. For instance, the Beginner group goes from Levels 1 through 9.

I also offer seminars on more specialized topics, such as building web-based databases, creating calendar-style forms, database security, image management, work order tracking, accounts payable, and learning SQL. Full details on all seminars are available on my website.

If you have questions about the material, I invite you to participate in the student forums on my website. If you are using my video player or the online theater, you will see the student forum automatically next to the class videos as long as you are connected to the internet. Here, you can read questions other students have posted, my responses, and any comments from other students. It is a good idea to review these discussions before each lesson, and of course, you are welcome to add your own questions and comments.

If you are not able to watch the videos online, you can still access the forums by going to my website.

For the most effective learning, I recommend you first watch each lesson all the way through without touching your computer. After that, replay the video, following along and replicating each step in your own database exactly as I show in class. Hold off on trying to apply these techniques to your own projects until you are comfortable and have mastered the examples from this tutorial.

If anything is unclear or if you get stuck, do not hesitate to rewatch the video or reach out to me in the forums.

The most important thing is to keep an open mind. Access can seem overwhelming in the beginning, but as you become familiar with it, you will find that it is user-friendly.

Let us now go through what is covered in this class.

We begin by creating and sending collection letters to customers with overdue, unpaid orders.

Lesson 1 covers building the order table and setting up a query to identify which customers should receive collection letters.

Lesson 2 involves taking that query and generating a report, giving each customer a personalized message.

Lesson 3 focuses on making collection letters customizable. We will make a letter table that stores separate letters for different aging periods: less than 30 days, 30 to 60 days, and greater than 60 days, for example. This table will allow you to specify starting and ending dates and generate a custom letter for each customer group.

In lesson 4, we will automate changing the date range when you select a particular collection letter. For instance, if you pick the 30-day letter, the form will automatically use a date range from 30 days ago up to today.

Lesson 5 expands the mass letter writing beyond collections. We will cover how to send letters to all active customers, as well as how to target customers based on lead source or category.

For a complete step-by-step video tutorial covering everything discussed here, visit my website at the link below. Live long and prosper, my friends.

Topic List

Creating an order table in Access
Setting up orders in the system
Generating collection letters for past due customers
Using queries to identify customers with unpaid orders
Building a report to send personalized collection letters
Creating multiple letter templates for different date ranges
Specifying start and end date ranges for collection letters
Automating customer selection based on letter and date range
Customizing form criteria based on chosen letter template
Utilizing the "is active" field to target active customers
Sending letters to customers by lead source or category
Broadening the letter writing form for various mailings
Generating mass mailings with custom criteria

Article

Welcome to an expert-level guide to sending customized letters from Microsoft Access. In this tutorial, we will build on the core concepts of creating mass mailings and show how to generate collection letters for customers with past due orders. We will walk through setting up the necessary tables and queries, creating customizable letter templates, and using Access features to target specific groups of customers, such as those who are active or belong to a particular lead source category.

We will start by constructing an order table, which will serve as the foundation for identifying customers who need to receive collection letters. For this, create a new table in your Access database called OrderT. Include fields such as OrderID (AutoNumber, Primary Key), CustomerID (Number), OrderDate (Date/Time), DueDate (Date/Time), Paid (Yes/No), and any other details relevant to your orders. Enter some sample data where some orders are unpaid and past their due dates.

Once the order data is in place, we need to identify customers with unpaid and overdue orders. Create a query that pulls customers with orders past their due date and not paid. You might use SQL like this:

SELECT CustomerT.CustomerID, CustomerT.FirstName, CustomerT.LastName, OrderT.OrderID, OrderT.OrderDate, OrderT.DueDate
FROM CustomerT
INNER JOIN OrderT ON CustomerT.CustomerID = OrderT.CustomerID
WHERE OrderT.Paid = False AND OrderT.DueDate < Date();

This query joins your customer and order tables, returning only those orders that are unpaid and overdue.

Next, we will send collection letters to these customers. To manage different letter types, set up a LetterT table. Include fields like LetterID (AutoNumber, Primary Key), LetterName (Text), StartDaysOverdue (Number), EndDaysOverdue (Number), and LetterBody (Long Text). For example, you could have one letter for accounts less than 30 days overdue, another for 30 to 60 days, and one for more than 60 days overdue. Populate your LetterT table with templates such as "Friendly Reminder" for 1-29 days, "Second Notice" for 30-59 days, and "Serious Delinquent" for 60 days or more.

To assign the correct letter to each customer, use a query that calculates the overdue days and matches them to the appropriate template. You can add a calculated field like DaysOverdue: Date() - [DueDate] in your query. Then create another query or join that filters letters where DaysOverdue falls between StartDaysOverdue and EndDaysOverdue.

Now, to actually generate the letters, build a report in Access. The report's RecordSource should be the query described above, ensuring every customer gets the right letter body. Insert fields for the customer's information and the LetterBody field from your LetterT table into the report. This will create personalized collection letters for each customer.

To make the system more flexible, add features to your letter process so you are not limited to just collection letters. For example, use the IsActive field from your CustomerT table, which you may have set up earlier. Add criteria in your queries to filter only active customers when sending promotional or informational letters.

Additionally, you can use the LeadSource field to further refine your selection. Suppose you want to send a letter only to customers who came from a specific advertising campaign. Simply add a parameter or dropdown on your letter generation form to select the LeadSource, then use it as a filter in your query.

If you want to automate more, set your letter form to update its date range fields automatically when you select a letter template. For example, if you pick the "30 to 60 days overdue" collection letter from a combo box, program the form in VBA to fill in the start and end date boxes automatically. You can use code like this in the AfterUpdate event of the combo box:

Private Sub cboLetter_AfterUpdate()
Me.txtStartDate = Date() - Nz(Me.cboLetter.Column(2),0) ' Column(2) = StartDaysOverdue
Me.txtEndDate = Date() - Nz(Me.cboLetter.Column(3),0) ' Column(3) = EndDaysOverdue
End Sub

This way, anyone using the database can select a letter template and have the system fill in the correct date range, making the process faster and reducing errors.

To send letters for any group or condition, simply add more filtering options to your query or form. For instance, you can let the user choose to mail all active customers, or only those in a certain category, or only those meeting custom criteria like recent purchases.

Remember to regularly test your queries and reports by generating sample output to ensure the right customers are targeted and the correct templates are used. With this setup, you can quickly adapt your mass mailing system to suit many different business needs, from collections to marketing to customer notifications.

Building and testing the database as you follow along is the best way to learn these techniques. If you ever get stuck, reviewing the logic of your queries and checking your table relationships will help you troubleshoot most mistakes. Mastering these tools will let you automate customer communication and build robust mail-merge features in Microsoft Access with ease.