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 2    dismiss
 
 
Courses - Microsoft Access 205
Description: Intermediate Microsoft Access 5
Running Time: 72 minutes
Pre-Requisites: Access 204 very strongly recommended
Previous Lesson: Access 204
Next Lesson: Access 206
Main Topics: Mass Mailing, Forms!FormName Notation, Multi-Column Reports, Print Post Cards
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    

Order before 1/20/2017 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 205
Intermediate Access 5

Using Our Letter Writer for Mass Mailings, Collection Letters, Generic Sales Letters, Columns in Reports, Post Cards. 72 Minutes.
 

AC205 Major Topics

  • Letter Writer - Mass Mailings
  • Forms ! FormName Notation
  • Multiple Column Reports
  • Printing Post Cards

In this class, we're continuing on with the Letter Writer we started building in Access 204. This time, we're focusing on Mass Mail - making it so you can send mass mailings directly from your database (without using an outside program like Microsoft Word). We'll make a series of collection letters, and a generic letter template for sending things like sales letters and such. We'll also see how to format reports to print Post Cards. If you do any mass-customer correspondence, this is a great class.

We will begin by adding some fields to our Customer table to track which customers have past-due amounts on their account, and what date their bills are due. It's a simplified version of what will come later - a full order-entry system where we can track invoices. But for today, this will do the job.

 

Next, we'll make a query to show us only the customers who have past-due amounts. We'll make it a parameter query so the user can type in the past due date (in case you want to see customers that are 30-days due, 60-days due, etc.)

 

Next, we'll learn all about the Wildcard Field (*) in queries. How you can use it to bring all of the fields from a table into a query, and why it's so very powerful.

 

Next, we'll edit the letter we started building in the last class for individual correspondence, and we'll make it work with our new query for collection letters.

Then, we'll build a new table and form to hold our letters - meaning we will create "stock" letters, like a 30-day past due letter. We can then select which letter we want to send out using this form!

 

We'll see how we can get values from a form into a report, just like we did by getting values across two forms.

 

Next we will learn how to force a new page to start after each customer record so we can print one customer letter to a page.

 

We will learn how to take the specific fields from our query and use them on the report for each customer.

 

We'll then add the capability for the user to enter in the date for the report right on the form - preventing us from having to type it in all the time. Again, this goes back to the topic of getting values across forms and reports.

 

Now that we've built a letter specific to sending collections, with just a little modification we can make this system generic - so we can send everything from sales and promotional letters to holiday greeting cards. We'll begin by adding a field to our customer table so we can track which customers to mail to.

 

We'll create a mailing list query (easy to do) and then create a separate letter report for this generic letter. We'll add a new button to our form to open this letter instead. Plus, I'll teach you a neat trick to make your forms look cool with some rectangles and other effects.

 

We will edit our Customer List Form to show a checkbox where we can quickly and easily add or remove customers to/from our mailing list.

 

Now since we've been working with mailings, we'll spend some time learning how to format a report to print post cards. We'll begin by learning how to turn columns on in our reports. This will let us print four post cards on a page.

 

Then we'll use the same techniques from creating our letter (in fact, I'll show you how to just 'borrow' some of the fields) to create our post cards.

 

Again, if you are interested in using your Access database to do any kind of mass mailing, customer correspondence, post cards, or anything of this nature - or if you want to learn more about formatting reports - don't miss this course!

 

Access 205 Outline
 
1. Past Due Customers
Add fields to CustomerT: AmountDue, AmountDueDate
Create a past due query
Wilcard Field (*) in queries

2. Letter Table & Report
Copy letter to work with new query for collection letters
Create a Letter table to hold your letter text
Create a Letter form
Bringing data from an unbound form field into a report

3. More Letter Report
Force new page after section in reports
Create a greeting line
Place AmountDue and AmountDueDate in letter report
The difference between DefaultValue and ControlSource
Place report date in unbound text box on LetterF

4. Generic Letter Report
Making the letter writer generic so we can send to anyone
Add IncludeInMailing field to CustomerT
Create a Mailing List query
Create a generic letter report
Form design tricks with rectangles
Create a button to open generic letter report
Add IncludeInMailing field to customer list form
Add IncludeInMailing field to customer form
Update main menu with button to letter form

5. Post Cards
Create a blank report to use for post cards
Columns in reports
Sizing the post cards properly
Copying data fields and labels from other reports
 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 205

Richard on 1/1/2007:  Using Our Letter Writer for Mass Mailings, Collection Letters, Generic Sales Letters, Columns in Reports, Post Cards. 72 Minutes.
John Robinson on 1/18/2008: Do you have any thing on Access 2007.
Richard Rost on 1/23/2008: Not yet, but I am working on them. I hope to have some basic Access 2007 courses out next month.
 Faxylady on 4/21/2008: Can you watch the courses online if you already own them? This would make things easier if one is not at his home computer.
Richard Rost on 4/26/2008: Faxlady, if you log on to your account at www.599cd.com/AccountInfo, you can click on the VIEW ONLINE link to watch anything in the Theater.
Samuel Afatsao on 9/18/2008: Dear Richard,
Your tutorials I purchased some few weeks ago has made a change to my understanding of database.
I am developing a database for my personal use by logging all surgeries I perform in order not to be chasing the statistician for help.
My questions are:
1. Is it right to create an input form from tables or from queries?
2. Is it correct to create patient diagnosis and surgeries performed using a subform on the main input form derived from tables since a patient can have many diagnosis and surgeries on a single visit?
I’ve attached my tables to this mail. Please pay attention to my relational tree and primary as well as foreign keys. I need you to inspect my work. There is more ahead with the work.
Thank you for your cooperation.
Samuel.

Richard Rost on 9/18/2008: Samuel, to answer your questions:

(1) It doesn't really matter whether or not you create your form from a TABLE or QUERY. If you need calculated values (like how I calculate a line total in my videos) then use a query. Otherwise a table is just fine.

(2) Yes, you are correct there too. If your Parent Form is based on the VISIT, then your subform could be based on diagnoses or procedures performed for that visit. In fact, you might want to have 2 or 3 different subforms (each based on a sub-table) for each visit: one for diagnoses, one for procedures, and perhaps another for medications subscribed. There's no limit.

Now, if you'd like to send me your database so I can take a look at it, make sure you ZIP it up or at least run it through a Compact & Repair so it's small. Email it to my personal email address: richard.rost@amicron.com. Make sure you leave the [599CD] in the subject line to bypass my spam filter.

Now, I can't promise that I can get to it right away, but I will look at it for you and make sure you're doing things OK. Make sure to send it in Access 2003 format as I don't have 2007 installed right now.

 Chris on 3/28/2010: This is a general question. I noticed your Forms and Report layout has a grid resolution of 24 points per inch. I had issues with mine in that it was in Metric and had no grid dots showing. I Solved that by changing my Locale info. However, no matter where I look, I cannot see anywhere where to adjust the resolution of the layout grid beyond the 10 per inch that I presently have. Is there some menu that I can access to change the present settings?

Reply from Richard Rost:

Yep. Go into the form design properties. Look for the GridX and GridY properties.

 John on 6/26/2010: When you brought in the CustomerT.* , you suggested to hide the AmountDueDate field with the criteria in it. Is there a reason why we need both DueDate fields? Could we place the criteria in the DueDate field brought over and delete the initial date field?
 William P. Mello on 3/25/2011: Access 205
Generic Letter Report (12:14) does not have a video associated with your lesson.

William P. Mello on 3/25/2011: Never mind my previous comment, after clicking on the Generic Letter Report (12:14)again, the video worked.

Sincerely,
William P. Mello

on 9/27/2012: In Access 205 you have put check boxes for "mailing list" in two different places. What if the one is checked in the Customer table, but not in the other place. Won't that cause confusion? Shouldn't data only be stored in 1 area?

Reply from Richard Rost:

I don't remember this. Can you tell me what lesson and time index, please?

Finola on 11/11/2012: Dear Rick, I have just completed this module and all seems to have gone well but for one slight problem - when I run my report with all the letters showing, every alternate record has the grey background that you see when you have a list of records. I am hoping it is just a question of uncheckking a box but I can't seem to find one! Could you point me in the direction please, so that all the records will have a white background. Many thanks, Finola

Reply from Richard Rost:

This is one of my pet peeves with Access 2010. I hate this setting. It should be an OPTION not a default. Go to Design View, right-click on the background of your form, select Properties. On the Format tab, you'll find Background Color and Alternate Back Color. Set the alternate the same as the regular background color (or NO COLOR) and that fixes the problem.

Finola Bromley on 11/12/2012: Thanks Richard - that solved the problem perfectly.
Regards
Finola

Finolab on 11/14/2012: Hi Richard. Can you tell me if there is a way that a generic letter sent to a group of clients can be stored in their individual contacts the same way that one-off letters are, so that there is a complete history of all letters sent to the client in the contacts table? Thank you.

Reply from Richard Rost:

Yes, this would be possible, but you'd need some way to automate adding the letter to each customer's history. You could use a loop with an SQL insert statement or a recordset. I'll be covering how to do this in my upcoming Access Email Seminar.

Richard Shafer on 3/3/2013: Hello. Working on the access 205 lesson towards the end of lesson 4 Generic Letter Report. You put the yes/no check box in the CustomerListF. I am using Access 2013 and when I do this, I am unable to check or uncheck this box. Is there something that I need to enable or disable in the properties to allow this? And if so, where? I can't seem to get my check boxes to check or uncheck when I click in the field. I have added this to the form, saved the form, closed it and then reopened it and it does not allow me to check or uncheck this box.

Reply from Richard Rost:

Did you BIND the check box to a FIELD (specify a control source)?

Rick Shafer on 3/6/2013: No, did not bind it. Control Source is IncludeInMailings which is what it comes up with when I add the field to the form. I have deleted it and added it again and it still will not allow me to check the boxes. When I go into the table that this field is based on, I can change it there. I just can't change it on the CustomerListF that it's added to.

Reply from Richard Rost:

Did you somehow set the ENABLED property to FALSE?

 

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