Hello,



I have a mail merge that I send out for people who's certifications are expiring (one year). I have been asked to add not only who is expired but who will expire certifications one month and two months out as well. I made three separate tables but how do I stop the merge in that table and move to the second one and then the third, as I select names it continues to fill my first table until its full before it moves to the second one even though they are different expatriation.

Attached Files

L-MEMO.doc (77.0 KB, 36 views)

To do this with Word's mailmerge tools, you would need to use three separate mailmerges, then combine the output documents. Even for that, though, your document isn't properly configured for handling variable amounts of data. To do that, you'd need to use Word's Catalogue/Directory Mailmerge facility (the terminology depends on the Word version). To see how to do so with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
http://windowssecrets.com/forums/sho...merge-Tutorial
or:
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

For some worked examples, see the attachments to the posts at:
https://www.msofficeforums.com/mail-...html#post23345
https://www.msofficeforums.com/mail-...html#post30327
http://windowssecrets.com/forums/sho...l=1#post928391

Alternatively, you may want to try one of the Many-to-One Mail Merge add-ins, from:
Graham Mayor at http://www.gmayor.com/ManyToOne.htm; or
Doug Robbins at http://bit.ly/1hduSCB

In addition to a 'Many to One' merge, the latter handles:
• Merge with Charts
• Duplex Merge
• Merge with FormFields
• Merge with Attachments
• Merge to Individual Documents
• Merge, Print and Staple

Rather than using a mailmerge, however, I'd be inclined to use three database fields for this. Doing so obviates the need to generate the three separate documents and means the document can auto-update any time you open it. Without knowing more about your data source and reporting requirements, I can't give more specific advice, however. If you could you attach a workbook to a post with some representative data (delete/obfuscate anything sensitive), I'll have a go at setting this up for you.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you,

I have attached a workbook that shows what I have, I deleted all of the personal info and shortened it to around 150 line items but you get the idea.

In the word doc that I want to merge this data to is in my previous post example all I need is "Last Name", "First name", "Rank" and "AA&E" Date to show in the mail merge.

Example of merge in three different areas:

Expired Certifications:
Smith, Jon GM1 1Jan2013
Doe, Joe SB2 5Jan2013
etc

One Month from Expiration:
Smith, Jane BM1 1Feb2013
Dow, Mike SB2 5Feb2013

Two Months from expiration:
Smith, Jon GM1 1JMar2013
Doe, Joe SB2 5Mar2013

Attached Files

Tracker.xlsx (19.0 KB, 25 views)

Try the attached. Simply save both files to the same folder. Note the extra column in the worksheet to calculate the expiry dates.

Of course, you may not want the shading I've used for the tables. The output format, including the shading, is controlled by field switches. To see the field codes and switches, press Alt-F9. For an overview of what they do and how they're used, see: http://office.microsoft.com/en-us/wo...010260104.aspx. That link refers to a 'Table AutoFormat dialog box', which you can access via Insert|Quick Parts|Field>DataBase.

Attached Files

CertificationExpiry.zip (30.4 KB, 191 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

That file is awesome, but well above my knowledge level which is why i am coming back to you for some assistance. I may not have explained the dates correctly to you. The date listed under the "AA&E" is the date they completed their paperwork and it will need to be completed again in one year from that date. The way it is pulling the data now for example "Adickes" is listed as Being due within 1 month in the Word Doc, but he just completed his paperwork 16Jun2014 so he is not due again till next year, and its that way for all of them. Does this make sense?

The only other thing is it possible to list the three sections as "Expired" meaning they are over the one year limit, "Due Within 1 Month" for those that will reach the one year in one month, and then "Due within Two months" for those that will reach the one year expiration in two months? Oh and the formatting is great, but can you add the actual expiration date to the list after the "Dept"

Again the file is great and I hate for you to have done all that for nothing but If this is too much to change dont worry about it, I dont want to have you doing to much, I just wish I knew more about this. If you have any questions please let me know or if its too much just let me know also. Thanks again.

Try the attached. I changed the 'Due' formula in Excel and added 'Overdue' (over 1 month) and 'Expired' (less than 1 month) sets to the report. Getting the dates to output in the same format as your workbook was the tricky part.

The three DATABSE fields are:
Overdue
{ DATABASE \d "{FILENAME \p}/../Tracker.xlsx" \s "SELECT [Last], [First], [Rank], [Dept], FORMAT([AA&E], 'DD-MMM-YYYY') AS Expiry FROM [Tracker$] WHERE [Due] < 0 ORDER BY [Last] " \l "9" \b "47" \h }

Expired
{ DATABASE \d "{FILENAME \p}/../Tracker.xlsx" \s "SELECT [Last], [First], [Rank], [Dept], FORMAT([AA&E], 'DD-MMM-YYYY') AS Expiry FROM [Tracker$] WHERE [Due] = 0 ORDER BY [Last] " \l "9" \b "47" \h }

Due Within 1 Month
{ DATABASE \d "{FILENAME \p}/../Tracker.xlsx" \s "SELECT [Last], [First], [Rank], [Dept] , FORMAT([AA&E], 'DD-MMM-YYYY') AS Expiry FROM [Tracker$] WHERE [Due] = 1 ORDER BY [Last]" \l "9" \b "47" \h }

Attached Files

CertificationExpiry.zip (29.0 KB, 109 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks that worked great! In case I need to change it, can you give instructions on how to change the color format? Not sure what my boss will want. Thanks again for the help!

also, if I right click and go to update field can i change the name of the file name if I want to use this for different sheets? Example, use what I have for the AA&E in one word doc and on Tracker sheet 2 make another word doc that pulls different dates for Training of sheet 2?

Regarding the formatting, changing that is fairly simple. For example, in the field codes you'll see:
\l "9" \b "47" \h
For a fairly simple grid, you could change that to:
\l "27" \b "165" \h
As indicated in post #4, the formatting options can be set via the 'Table AutoFormat dialog box', which you can access via Insert|Quick Parts|Field>DataBase. All you need to do to see what's possible is to use the process to connect to your data source, then go though the various 'Table AutoFormat' options and see which combination you (or your boss) prefer.

To change the source filenames, you'd need to edit the fields. You do this by Pressing Alt-F9, which will turn the field codes into something like:
{ DATABASE \d "{FILENAME \p}/../Tracker.xlsx" \s "SELECT [Last], [First], [Rank], [Dept], FORMAT([AA&E], 'DD-MMM-YYYY') AS Expiry FROM [Tracker$] WHERE [Due] < 0 ORDER BY [Last] " \l "9" \b "47" \h }
To use a different file in the same folder, simply change 'Tracker.xlsx' to whatever that other file's name is. If you want to use a different worksheet, change 'Tracker' in [Tracker$] to whatever that other sheet's name is. When you're done, press Alt-F9 again to toggle the field code display, then Ctrl-A, F9, to update the display.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]