Mail Merge / Page break on record change

[Scott]

Trying to use the mail merge feature in Word 2003 to merge data from an excel
file, but I need the merge to break when there is a change in the data - a
data file would look like this.

Custid Name Address1 Address2 Dept Desc 2006 Prod
2005 Prod
100 Test 123 MAIN TEST, GA 901 AUTO 1568.90
500.95
100 Test 123 MAIN TEST, GA 903 PROP 485.00
0.00
100 Test 123 MAIN TEST, GA 932 P&C 3468.50
300.00
200 ABC XYZ MAIN 123, GA 908 PEST 2589.00
0.00
200 ABC XYZ MAIN 123, GA 933 BOAT 589.00
297.76
200 ABC XYZ MAIN 123, GA 901 AUTO 459.00
678.09
200 ABC XYZ MAIN 123, GA 923 HOME 420.00
2000.00

I want the mail merge to page break when the Custid changes, but before it
breaks, I want to give a grand total for the 2006Prod and 2005Prod Columns.
Also just print the Custid, Name, Address1, Address2 once on the page. This
would later be printed and folded to be mailed in a window envelop. So the
net effect would look something like this:

100 Test 123 MAIN TEST, GA

901 AUTO 1568.90 500.95
903 PROP 485.00 0.00
932 P&C 3468.50 300.00

----------- page break -----------------

200 ABC XYZ MAIN 123, GA

908 PEST 2589.00 0.00
933 BOAT 589.00 297.76
901 AUTO 459.00 678.09
923 HOME 420.00 2000.00

Hope this makes sense. I need help really really bad - Thanks in advance
for your time.

 

[Graham Mayor]

See How to use mail merge to create a list sorted by category in Word 2002 -
http://support.microsoft.com/?kbid=294686

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


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

 

[Scott]

thats what I am trying as we speak, but I cannot seem to make that work
either. do you have a sample word document i could use as a template to
figure this out?

 

[Doug Robbins - Word MVP]

See the "Group Multiple items for a single condition" item on fellow MVP
Cindy Meister's website at

http://homepage.swissonline.ch/cindymeister/mergfaq1.htm#DBPic


Or take a look at the following Knowledge Base Article

http://support.microsoft.com/default.aspx?scid=kb;en-us;211303


http://www.knowhow.com/Guides/CompoundMerges/CompoundMerge.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 should 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