I am looking for help in finding a way to accomplish the following task involving the merging of data from an Excel database to a Word document using the mail merge feature.

In my Excel database, I have columns (fields) related to 8 services available to a particular record. These 8 services can be shared amongst 2 or more crews.

A Word merge document is created for each crew, listing each record and the fields associated with that data, including any of the 8 services that crew may be responsible for. However, the mail merge document is capable of providing only 4 services for each crew.

Consider the following Excel data:

r1_crew = hpe
r2_crew = hpe
r3_crew = hpe
r4_crew = hpl
r5_crew = hpl
r6_crew = cul
r7_crew = ""
r8_crew = ""

There are three different crews responsible for providing the up to 8 services. Each crew will have it's own mail mail document created, for which the services they are responsible for will be documented.

However, there are only 4 fields available in the mail merge document...
r1_crew
r2_crew
r3_crew
r4_crew

But I don't don't how to ensure that the three crews are given their services since the report only covers r1_crew through r4_crew, and not r5_crew through r_8_crew.

The reports need to report the following

Report: hpe
{MMfield : r1_crew} = r1_crew
{MMfield : r2_crew} = r2_crew
{MMfield : r3_crew} = r3_crew
{MMfield : r4_crew} = ""

Report: hpl
{MMfield : r1_crew} = r4_crew
{MMfield : r2_crew} = r5_crew
{MMfield : r3_crew} = ""
{MMfield : r4_crew} = ""

Report: cul
{MMfield : r1_crew} = r6_crew
{MMfield : r2_crew} = ""
{MMfield : r3_crew} = ""
{MMfield : r4_crew} = ""

in other words, atthough r4_crew represent he fourth service overall, it is the first service for HPL, and r6_crew is the first service for CUL. The shared mail merge document have only fields representing r1_crew through r4_crew, so really, only the HPE's services will be merged.


I hope I've provided a clear enough explanation of what I hope to accomplish. It was difficult to put in into words.

I am unsure whether the assignment of the different services needs to reassigned somehow in excel, or it it can be done at the point of the mail merge.