# Thread: Word Mail Merge Directory Calculation Problems

1. ## Word Mail Merge Directory Calculation Problems

I'm making a mail merge Directory and have got it pretty much doing what it's supposed to. This tutorial was invaluable: https://windowssecrets.com/forums/sho...merge-Tutorial

I have a list of sales figures in the directory, and I can successfully add up the totals per salesperson but only if the total appears below the table of repeated data.

For example, I would like the letter to read:

Congratulations Josh! This month your totals were \$3,500. Here are the details of your transactions:

7/7/13 \$1,000

7/8/13 \$2,000

7/9/13 \$500

Again, I can total those numbers if the total appears below, but I can't display a total above. In my letters the above total appears as \$0.

Any ideas how I can do this? Thanks!

2. There are two ways to handle this.

Assuming your data source is a worksheet or a database table, the easiest solution is to add the calculation as another column in that source, and just use the merge field for that column in the first line of the letter. That's especially useful if the calculation involves logic and not just a simple sum, or if it needs to include a variable number of details.

The other way is to construct the total field as a formula field, with copies of the detail merge fields nested inside; for example

{ = { MERGEFIELD Q1 } + { MERGEFIELD Q2 } + { MERGEFIELD Q3 } + { MERGEFIELD Q4 } }

3. Unfortunately, this is going to be a large mail merge using data that's exported into an Excel worksheet. Adding columns and manually creating calculations in Excel isn't realistic. I was hoping to be able to do all of that in the Word mail merge. I've already created a formula that looks like this:

{SET MyTotals{=MyTotals+{MERGEFIELD Daily_Sales}}}.

I've placed it right after my repeated data. At the top of my document I've got {SET MyTotals 0} to reset the calculation after each salesperson. In the letter, if I put {=MyTotals\#\$0,0} in a place where it appears below my list of sales numbers, it works perfectly.

My problem is that I want {=MyTotals\#\$0,0} to appear in the letter above my list of sales numbers. It's showing up as 0. I think it's getting the zero from my {SET MyTotals 0} at the top of the document, but if I delete that, the calculations don't reset after each salesperson.

4. Our Database Manager solved all of my problems. He included the calculation in our export to Excel, so I don't have to do any formulas in Word. I like a good riddle, but it seems that Word mail merge directories just can't handle displaying their calculations above the listed numbers. Oh well.

5. You can also do this by modifying the SQL Query which is how Word queries the data source. I don't know if you can do this via the GUI but you can do it by using VBA

To get hold of the current SQL string you can use
Debug.Print ActiveDocument.MailMerge.DataSource.QueryString

Then you can alter this query to create the total so it appears in the mergefield list such as
ActiveDocument.MailMerge.DataSource.QueryString = "SELECT Name, Apples, Bananas, Pears, (Bananas + Apples + Pears) as Fruit FROM `Sheet1\$`"

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•