Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2013
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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: http://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. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Willow Grove, Pennsylvania, USA
    Posts
    205
    Thanks
    4
    Thanked 49 Times in 40 Posts
    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. #3
    New Lounger
    Join Date
    Jul 2013
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.

    I hope this made sense.

  4. #4
    New Lounger
    Join Date
    Jul 2013
    Posts
    9
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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. #5
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    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$`"
    Andrew Lockton, Chrysalis Design, Melbourne Australia

Posting Permissions

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