Mail Merge: How to begin? 1

[KerryL]

I have a database that will need to be used as a source for addressing letters. I know how to do a mail merge in Word, but I don't know how to use the data in my customer table for addressing letters, or envelopes or address labels.

How would I go about creating options for my users to address form letters using the names/addresses from the customer table?

Also, I would like to allow them to address envelopes in the same manner.

The users will need the ability to address letters & envelopes using ALL records, or select which ones to print based on one of the fields (an org name) in the table.

Any advice I can get on where to begin will be helpful.

Thanks,
KerryL

 

[dhookom]

I like to create a separate merge file that can be used for mail merge rather than attaching a table or query as a datasource for the merge document. To view one method of filtering and creating the merge file, check out the DH Query By Form at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KerryL]

Thanks Duane,
I read your DH Query By Form info, but I'm not sure if I completely understand it.

Does it create a separate merge file that is then used by the user by opening Word and doing a mail merge from within Word?

If so, how is that different or better than exporting the results of a query using the Tools>Office Links option?

If not, what is it that I'm not understanding?

Thank you for your help,
KerryL

 

[dhookom]

KerryL
Yes, the QBF creates a separate file. I prefer a separate file so that it doesn't require Access to be open. You can also save the merge file for future reference or send it to someone else to do the merge. The Office Links option requires that Access be open during the merge.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KerryL]

I downloaded & opened your DH QBF applet DB. I looked at a couple of existing reports and created a couple of new ones using the main form/query builder. I think I understand what it's doing, and it looks like it might work well as a front end to create queries for mailing labels, mail merges, etc. in my DB.

The DB I'd like to try it on has only two tables at this time, so I'm wondering if this is overkill. And I have to be honest with you, it seems a little over my head. I'm not sure where to begin to integrate it into my DB.

I read your explanation on the QBF's web page, and the instructions in the applet. If I import it as explained in the application, does it find my DB's tables and allow the user to search for the corresponding criteria automatically?

I guess I'm not completely sure what to expect or how to make it work for me.

Thanks for your patience,
KerryL

 

[dhookom]

KerryL,
It takes just a little setup. You need to import the tables and forms from the sample into your mdb file. Then you just create one or more queries based on your tables. This could be as easy as creating a query like SELECT * from tblYourTable. Save the query with a name beginning with &quot;<&quot;. All queries that begin with &quot;<&quot; become available as datasources within the query by form. Queries work well because you can combine more than one table, make totals queries, alias field name etc.

You will want to delete the sample records from the two tables so that your users don't get confused. If you have any questions, come on back to this thread or start a new one.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KerryL]

Duane,
I imported the forms and tables but I don't understand why I need the tables. Why do I need to import the tables from the QBF database--they contain data I don't want to query on in my database.

BTW, when I launch the QBF applet from within my database, I assume the form I should us is &quot;frmQuickReportMenu.&quot; Correct?

 

[dhookom]

You only need the two tables from the sample. These are the QBF tables. The best method of opening the applet is frmQuickReportMenu.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KerryL]

Duane,
Imported everything and got it working, but when I click on any of the options (Modify Report, New Report, etc) I receive a &quot;Compile error: User-defined type not defined.&quot;

The debugger takes me to the code below and highlights this line: &quot;Dim db As DAO.Database&quot;

/begin code
Private Sub Form_Current()
On Error GoTo Form_Current_Err
If IsNull(Me.QBFID) Then
Exit Sub
End If
Dim toss As Integer
Dim i As Integer
Dim db As DAO.Database
Dim iLegit As Boolean
iLegit = False
Dim qdef As DAO.QueryDef
Set db = CurrentDb
For Each qdef In db.QueryDefs
If qdef.Name = Me.cboSource Then
iLegit = True
End If
/end code


In fact, if I attempt to compile my database now, I get a compile error at &quot;Dim qdef As DAO.QueryDef&quot; or wherever the DAO property is called out. Do I need to set my datasource type (?) to accommodate DAO to avoid the compile errors?

Thanks,
Kerry

 

[dhookom]

Good work on debugging...
You can open any module and select Tools|References. Scroll down to find Microsoft DAO 3.x ... and check it. Close the references and recompile. You should be good to go ;-)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KerryL]

Yeah, I thought it had something to do with the datasource type or something like that, but I don't get into that area very often.

I set the reference and it compiled just fine. Now to tweak and modify it so it's customized for my application.

Thanks, Duane.

 

[KerryL]

Duane,
Sorry to bother you again, but I have a question.

I was able to create a basic query from which to create new reports, but the way I link contacts to their corresponding org category is causing a problem for my reports. Let me try and explain.

Each record in tblContacts contains 4 fields (Cat1-Cat4) linking the contact to specific organization categories. (For example, John Smith may be linked to the Boy Scouts in Cat1, Big Brothers in Cat2, etc) They're linked via the indexed field, which is the CatID field.

When I create a new report by limiting the list to a specific Category, the Criteria Value field shows the CatID instead of the Category Name. So if I limit the report to Contacts where Category 1 = Boy Scouts, it shows a number 8 (CatID) instead of the actual name of the category.

This is because instead of populating the Cat1 field with the name of the Category when creating or editing Contact records, I used the Category ID instead. That way, if the name of a Category changes, the contact records are updated globally.

The easiest fix would be to populate the Cat1 field with the text name of the Category, but as I said, that would make it harder to update records if a Category name changes.

What's the best way to deal with this? Do I need to rename all the table fields with recognizable names and link to those instead of the key fields?

How can I get the Criteria Value to display the name of the corresponding Category even though the CategoryID is what resides in the field it's grabbing the value from?

If further explanation is needed please let me know. Any suggestions you have will be greatly appreciated.

Thank you,
Kerry

 

[dhookom]

You can include the table with category names in the query. Since you have 4 categories, you will need to include it 4 times.

This is not the way to create a good application. What happens when someone needs more than 4 categories? The proper method would be to have a related table &quot;tblContactCats&quot; with fields like
ContactID
CatID
Active
Then each category a contact was in would create a new record. This makes querying and other tasks much easier.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KerryL]

Thanks Duane, I was able to use the Category names instead of the Category ID after adding the table 4 times.

It's very unlikely any of the contacts would have 4 categories (only a few even have 3) but I see your point about limiting it. If you're willing to help me learn how, I'd rather create a good application by doing this right, as you say.

If I understand correctly, creating a &quot;tblContactCats&quot; that links ContactID with CatID would allow the number of Categories per Contact to expand as needed instead of being limited to 4 each.

I'm not sure how to relate that table to the others, but more importantly, I wouldn't know how to create forms and reports that &quot;expand as needed&quot; depending on the number of categories each contact has.

For instance, if I have a form that allows users to edit a Contact's data, how would the form list 2 Categories for John Smith, but 6 Categories for Billy Taylor? Likewise with a report that lists Contacts for a region; how do I design it so it shows 2 for Smith and 6 for Taylor?

Thanks for your input,
Kerry

 

[dhookom]

Kerry,
The easiest way to comprehend this is to look at the Northwind.mdb. Consider each Contact as a Sales Order and each Category for that Contact as the Sales Order Details (product purchased). You your main Contact form would be similar to the Sales Order form. You would include a Category subform similar to a Sales Order Details subform. Just like a Sales Order could contain from 1 to unlimited number of Details (purchased products), the number of Categories per Contact is unlimited.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KerryL]

Quick question:

If I change how I link Contacts & Categories to the method you suggest, will the search critera in your QBF applet work better?

Because what I'm finding out is that I cannot create a report that lists all Contacts that have a specific Category in Cat 1, OR Cat 2 OR Cat 3.

IOW, if I want to create a report showing all records with 'Boy Scouts' in Category 1 or Category 2, it's not working. Instead, the report lists only records with 'Boy Scouts' in Category 1 AND Category 2, which of course will never occur.

Likewise, if I want to build a report showing all Contacts with 'Boy Scouts' or 'Boys Clubs' as one of their 4 categories, that doesn't work either because it finds only exact matches where Cat1=Boy Scouts AND Cat2=Boys Clubs.


If I change the Contact/Category relationship to your suggestion, I assume the report will produce a list of all Contacts containing 'Boy Scouts' as a Category no matter how many others they're also linked to. Correct?

 

[dhookom]

If you use the proper normalization, then the QBF will work better. There is an option in the Operator column &quot;Contains&quot;. You could enter Boy Scouts,Boys Clubs. Or, you could select the Like and enter Boy.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KerryL]

Yes, I tried the &quot;Contains&quot; and &quot;Like&quot; operators. But it still limits the results to records with categories that &quot;contain&quot; or are &quot;like&quot; the entry in search criteria field #1 AND search criteria field #2. It finds records that match both fields exactly, not one or the other (or both) as I had hoped.

But possibly if I change the way contacts and categories are linked I can avoid this problem.

 

[KerryL]

Duane,
I understand the sub-form concept as a way of allowing the form or report to automatically expand as needed. Thank you for that explanation.

The problem I'm having is building relationships for the tables in a way that helps me create the application &quot;the right way&quot;

If I undertand you correctly, I'll need just three tables with these relationships:

tblContacts
tblCategories
tblContactCats

tblContacts.ContactID ---> tblContactCats.ContactID (1-to-many)
tblCategories.CategoryID ---> tblContactCats.CategoryID (1-to-many)

This means each contact can have multiple records in tblContactCats and each category can have multiple records in tblConactCats.

Does this make sense--is it that simple--or am I missing something?

Thank you,
KerryL

 

[dhookom]

It's that simple. Your subform will be based on the junction table (tblContactCats).

Duane
MS Access MVP
Find out how to get great answers faq219-2884.