merge trouble shooting (Word-97 SR2)

[jazman2k]

I'm trying to perform a merge based on an Excel doc.
The datasource has multiple entries for the field MiscContract No.
How can I get the merge to honor this and create a new row in the table for each MiscContractNo?

BTW-The Totals are done in the Excel sheet.
This is NOTa dynamic calculation.

[jazman2k]

Here's a version of the data source to help illuminate.
I've color coded a few of the records that should be grouped in the same table
before the new document is created during the merge process.
I've omitted the numbers.
Hope that isn't confusing doing it without.

[Phil Rabichow]

Hi Jazman:
It's been awhile since I looked at something like this. I copied a tip some time ago from a couple of sites, one being Microsoft KB & another being a website by Leigh Weber. Unfortunately, I did not copy down the URLs at that time. Nevertheless, the following will get you started:

WD97: How to Merge Conditional Number of Records to the Same Page (Q105888)

How to Use Mail Merge to Create a List Sorted by Category

Also, this conglomerate tip may help:

Mail Merge: Group multiple items for a single condition
Sometimes, you want to create a mail merge where you can list several items for one particular category, such as the invoices for each customer. Word does not have an automatic facility for this kind of merge, but there are workarounds.

1. You can use the InsertDatabase field to link the category information from the merge to the same category from the database. The list will be in a table. There is an excellent explanation and sample file at Leigh Weber's website. The main drawback of this method is the restricted possibilities for formatting.

2. A combination of IF and SET fields can check whether a merge record meets the criteria, if it does the information is listed, otherwise the merge moves to the next record. The Microsoft Knowledge Base article Q105888 gives a detailed example.

To merge a conditional number of records to the same page, use a combination of SET, IF, and NEXTIF fields. For example, you could use this method to merge all records with the same name to one page in the merge and create a new page in the merge when a new name is encountered in the data file.

The following example uses a data file with employee names (Employee) and projects they are working on (Projects). The Check field is used in the data file to mark the last record of a particular name.

Sample Data File:

Employee Project Check
John Doe 4578j
John Doe 86785x
John Doe 543p 1
Jane Doe 87x
Jane Doe 89976m 1
Sam Smith 7897r
Sam Smith 857t
Sam Smith 78974x
Sam Smith 7868p

The main document should be a form letter set up as follows, (with a copy of the conditional statements equal to the maximum number of projects for each employee-four in the above example):

NOTE: To type field brackets ( { } ) in a Word document, press CTRL+F9. To turn field codes on or off, press ALT+F9.

Employee Project
{MERGEFIELD Employee} {MERGEFIELD Project}{set duplicate {if {MERGEFIELD Check}="1" "off" "on"}}{nextif {mergefield check}=""}{if {duplicate}="on" "P
t{MERGEFIELD Project}"}{set duplicate {if {MERGEFIELD Check}="1" "off" "on"}}{nextif {mergefield check}=""}{if {duplicate}="on" "P
t{MERGEFIELD Project}"}{set duplicate {if {MERGEFIELD Check}="1" "off" "on"}}{nextif {mergefield check}=""}{if {duplicate}="on" "P
t{MERGEFIELD Project}"}{set duplicate {if {MERGEFIELD Check}="1" "off" "on"}}{nextif {mergefield check}=""}{if {duplicate}="on" "P
t{MERGEFIELD Project}"}

Where "P" represents a paragraph mark that pushes the next Project to a new line, and "t" represents a tab character that aligns the next project under the previous project. Note that you can type the "P" & it will work, but you substitute a real tab for the "t". Also, if you have several mergefields to go with each employee, put them all in before, one after another, or with a tab in between to line them up, in place of {MERGEFIELD Project}.

The resulting merge document appears as follows:
Employee Project
John Doe 4578j
86785x
5436435p
--- Page Break ---
Jane Doe87x
89976m
--- Page Break ---
Sam Smith7897r
857t
78974x
7868p

Suppose you have numerous fields to repeat for each name:
e.g.
InsLastName InsFirstName ClassName ClassNum HrsCredit Time Ch
Jones Bob History 1 464 3 8:30
Jones Bob Government 489 3 1:00 1
Smith Sandy Biology 1 789 4 8:30
Smith Sandy Biology 2 759 4 2:30
Smith Sandy Anthropology 758 3 4:00 1
Wells Orsen Acting 3 256 2 10:30

Use the following format:

{ MERGEFIELD InsLastName }, {MERGEFIELD InsFirstName } { MERGEFIELD ClassName } { MERGEFIELD ClassNum } { MERGEFIELD HrsCredit } { MERGEFIELD Time }{ set duplicate { if { MERGEFIELD Ch}="1" "off" "on"} }{ nextif{ MERGEFIELD Ch }=""}{if { duplicate }="on"}
{ MERGEFIELD ClassName } { MERGEFIELD ClassNum } { MERGEFIELD HrsCredit } { MERGEFIELD Time }{ set duplicate { { MERGEFIELD Ch}="1" "off" "on"} }{ nextif{ MERGEFIELD Ch }=""}{if { duplicate }="on"}
{ MERGEFIELD ClassName } { MERGEFIELD ClassNum } { MERGEFIELD HrsCredit } { MERGEFIELD Time }{ set duplicate { { MERGEFIELD Ch}="1" "off" "on"} }{ nextif{ MERGEFIELD Ch }=""}{if { duplicate }="on"}

Note also: It doesn't seem to matter whether, after the first { set duplicate }, the next field has an if or not. Either way works in Word 95.

[The above lines may not copy well in an email. The 't' represents a tab & the 'p' represents a paragraph mark (<enter>)]. You must repeat the mergefield lines to be the maximum number of times that an instructor has records (in the above table it's 3). The result will be:

Instructor CourseName Course Number Credit Time
Jones, Bob History 1 464 3 8:30
Government 489 3 1:00
Government 489 3 1:00
-----------Section Break-----------
Instructor CourseName Course Number Credit Time
Smith, Sandy Biology 1 789 4 8:30
Biology 2 759 4 2:30
Anthropology 758 3 4:00
-----------Section Break-----------
Instructor CourseName Course Number Credit Time
Wells, Orsen Acting 3 256 2 10:30

3. You can create a user-defined function in your database program that concatenates all items for the list into a single string. Place this function in an expression in the query you will be using as a datasource so that it can be selected as a single field in the mail merge. Please note that this method only works when the merge link method is DDE; ODBC drivers do not recognize user-defined functions. An example of such a function and a query using it is in the file "WdAcc95.zip" for Office 95; (51 KB)WdAcc97.zip for Office 97. (131 KB).

4. If none of these methods is satisfactory, then you can forego the built-in mail merge process entirely and use OLE-Automation or DDE to create your Word documents from your database application or a VB or Delphi interface.

I provide an example with sample files of using OLE-Automation to insert information from an Access 95 database into Word. Office 97 gives you even more flexibility in the approach you can take because Word97 VBA allows you to use OLE Automation and DAO to connect with Access database objects, or ODBCDirect to use other database formats. As for Office 95, you can download a zip file with sample database, templates and explanatory documents.

Sorry for the long reply, but I haven't had the time to sort through this in a long time.

[jazman2k]

Actually I have no problems with verbose replies and
I am not one to complain of 'information overload'.
In fact, if there was a way to just do a core dump,
I would happily download and parse out the info at my leisure.

That being said, I was sure that there was some script
I could whip up that would do a
For each loop in VBA.

How could they make it so complicated?
Surely this isn't that special of a request...

[Phil Rabichow]

Hi Jazman:
I know how you feel. You would think that certain common merges could be done more easily (i.e. with less thought). Since you don't complain about information overload <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, you might also take a look at Cindy Meister's website. She has a lot of information regarding mail merge that's just outstanding.