Duplicate in mail merge from Access Query

[Phil]

Hi,

I'm in word and want to do a mail merge using an Access query as the data
source. The query has dupliicate entries therefore I get duplicates in my
mail merge. I look at the Special Merges at CIndy Meister's website (got
this from the posting before this one), but don't know how to enter the code
or the exact syntax. It looks like what I need. Also is MERGEFIELD a key
word? I'm merging Last_name, First_name, Street, City, State, Zip.

Thanks in advance......

 

[Doug Robbins]

Can you explain what it is that you are trying to achieve. I get no idea at
all from your post.

I will say however that it is quite likely that what you want to do can be
done more easily with a report in Access.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[Phil]

Hi,
I have an Access DB with a query that pulls data from a master table (name,
address, city, state, zip, etc) and a transaction table (transaction type,
date, amount, etc). I want to merge the name/address info from this query to
various existing Word documents and new ones. Because the transaction table
may have more then one entry per person, I can get duplicate letters in my
mail merge.

 

[Graham Mayor]

If you enter a merge field from the toolbar its appearance in Word is
<<Last_Name>>. If you enter the field directly from the keyboard - you press
CTRL+F9 to give you the field boundaries {} and type between them Mergefield
Last_Name - thus
{Mergefield Last_Name} and <<Last_Name>> represent the same thing.


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Doug Robbins]

OK, so it is a "multiple items per condition" type document that you are
trying to produce in which case, I would do it all in Access with a
Report/Subreport seeing as that is where you have the data already.

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

One way is to use the method in 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

But it also sounds like you need to see the article "What do I do with
macros sent to me by other newsgroup readers to help me out?" at:

http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm


--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP