Address duplication problem

[Bill Thompson]

I have an Access database that I have ran a query on and want to do a mail
merge from that query. My problem is that the database has multiple names
with the same address and I only want to print one name and address label
per address. For example the database has:

Tim Smith 123 W. 99th Clark Iowa 12345
Jane Smith 123 W. 99th Clark Iowa 12345
Freddy Smith 123 W. 99th Clark Iowa 12345

I want to send a letter to:

The Smith Family
123 W. 99th
Clark, Iowa 12345

I need to eliminate the duplicate addresses so that each is unique. Is
there a way to do this in the mail merge part of word or is there a way to
do this in the access query? Please help.

 

[Doug Robbins - Word MVP]

Nothing's impossible, but it cannot be done with a straight forward
mailmerge.

If the first name and the last name are in separate fields and you don't
have any

Bill Smith jr.

or

Bill Smith III

Then you could do it in Word by first executing a catalog or directory type
mail merge, then running a macro over that document to create a new document
that would create a new data source consisting of the unique records plus a
single instance of the address where there are multiple addressees with the
name of the addressee changed as to "The Smith Family" as in your example.

The following macro, that I developed for another purpose, does not do what
you want at the present, but it is what I would use as a starting point to
develop one to do what you want:

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.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
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

Alternatively, I am pretty sure that you could do this in Access. I am not
sure though whether it would be any simpler.
--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP