Nested mail merge

[Lennert Acke]

Hi,

I would like to merge data from an Access database to a Word document
via the Mail merge function.

But I have a problem. For each customer a document is needed containing
his contact information, but inside this document extra data is
necessary, his orders.

In Access you can get the different data by means of 2 different
queries. In Word however, it is only possible to access one single data
source.

Is it possible to retrieve data from a database in Word, based on the
information of the current record in a Mail merge ?

Any help appreciated,

Lennert Acke.

 

[Guest]

Hi Lennert,

I am not an expert with Microsoft's Mail Merge capabilities but if you can
not find a solution, there is another way of addressing your problem. I
have a commercial software package, OctoTools, which is capable of taking
information from your access database (or any text output from other
applications), combining it with a designed template (with OctoTools and/or
Word) and outputting a current record/file to print or convert to PDF for
electronic distribution and e-mail automatically. OctoTools is designed for
automatic processing of medium to high volume throughputs and is not
justified for a one at a time desktop output applications. If you are
interested, take a look at www.octotools.com or give me a call at 978
535-7676 (Boston, MA US) or drop me a quick note with any questions.

Thanks,

Larry T.

 

[Doug Robbins]

It's a lot easier to use a Report in Access as it has this capability.

Word does not really have the ability to perform a "multiple items per
condition (=key field)" mailmerge.

See the "Multiple items per condition" item under the "Special merges"
section of fellow MVP Cindy Meister's website at

http://homepage.swissonline.ch/cindymeister/MergFram.htm

Or, if you create a Catalog (on in Word XP and later, it's called Directory)
type mailmerge main document with the mergefields in the cells of a one row
table in the mailmerge main document with the keyfield in the first cell in
the row and then execute that merge to a new document and then run the
following macro, it will create separate tables with the records for each
key field in them. With a bit of further development, you may be able to
get it to do what you want.

' Macro to create multiple items per condition in separate tables from a
directory type mailmerge

Dim source As Document, target As Document, scat As Range, tcat As Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k - 1)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
j = ttab.Rows.Count
For i = 1 To stab.Rows.Count
Set tcat = ttab.Cell(j, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat <> tcat Then
ttab.Rows.Add
j = ttab.Rows.Count
ttab.Cell(j, 1).Range = scat
ttab.Cell(j, 1).Range.Paragraphs(1).PageBreakBefore = True
ttab.Rows.Add
ttab.Cell(j + 1, 1).Range.Paragraphs(1).PageBreakBefore = False
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
Else
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
End If
Next i


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Lennert Acke]

Thank you for all your responses.

I looked in to the methods proposed by Cindy Meister but they don't
seem like an ideal solution.

I will now look into the Access reports method.

Thank you very much.

Regards,

Lennert.