Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Mail Merge

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-28-2012, 07:20 AM
daymaker daymaker is offline Windows XP Office 2000
Novice
 
Join Date: Feb 2012
Posts: 22
daymaker is on a distinguished road
Default Access to Word, Creating a list from multiple records


(Using Access 2000 on my computer, but we have another computer in-office that has Access 2010.)

We have an Access table that we're wanting to use in creating letters that go out to our customers so they know what all they have ordered from us in the last year. Here's a snapshot of a portion of the file's columns & records for reference:



For each customer we have, we assign them an 'Account' number (as shown). Many accounts order multiple times from us. We are wanting to merge certain fields (such as 'Order Number', 'Company', 'Title', 'Order Qty', 'Item', 'Order Date') per record for each Account into one Word template so that we only have to send them one letter with a list of order details rather than multiple letters for each record in the table. Is this something that we can manipulate within the Access table? Maybe this would be driven by the Word template rather than an Access manipulation? Or do we need to convert this Access file to an Excel file to make this possible?

Thanks in advance for any suggestions.

John
Reply With Quote
  #2  
Old 03-01-2012, 02:04 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,294
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi John,

You can use Word's Catalogue/Directory Mailmerge facility for this (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...ngs-(2000-2010)
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 a recent, worked example, see the attachment to post #13 at: http://www.msofficeforums.com/mail-m...nt-dollar.html

Alternatively, you may want to try the Many to One utility on the MergeTools Add-in that you can download from the following page of Doug Robbins' Windows Live SkyDrive: https://skydrive.live.com/?cid=5aedc...615E886B%21111
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 03-02-2012, 02:58 PM
daymaker daymaker is offline Windows XP Office 2000
Novice
 
Join Date: Feb 2012
Posts: 22
daymaker is on a distinguished road
Default

Paul, thanks for the reply. It looks like you have this sort of procedure really figured out. I downloaded the files, went through the tutorial and tried to understand it the best I could. This sort of thing is really over my head, and I'm trying to find my way through the dark.

I applied the steps to my own mail merge attempt and am having trouble getting the Key field to not repeat so that the data that relates to a certain key value gets displayed in list form. Here are some screen shots to hopefully give you an idea of what's going on.


Data source (notice the IF field, which did result in effectively totaling individual key 'Total Sales'):




Word document with merge fields:




Merge fields with codes revealed:




Merged document results:



And of course, when I put a page break in the Word merge document (via your instruction of CTRL-ENTER), the result is a different page per entry rather than consolidating all the order info per customer name (key).

Any ideas what I'm doing wrong?
Reply With Quote
  #4  
Old 03-02-2012, 06:38 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,294
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi daymaker,

I take it you're using the tutorials 'Using an Extra Field to Generate the Trailing Text Per Group' example as the basis for your mailmerge. If you select that example's field code and press Shift-F9, or if you just press Alt-F9 to expose all of the document's field coding, you should see:
Code:
{QUOTE{IF{MERGESEQ}= 1 {SET Key ""}}"{IF{MERGEFIELD State}<> {Key} "{IF{MERGESEQ}> 1 "{QUOTE 12}"}Director,
Retail Sales { MERGEFIELD State}
These are the sales figure for {MERGEFIELD State \* Charformat}{SET Key {MERGEFIELD State}}
City Representative Sales
"}{MERGEFIELD City} {MERGEFIELD Representative} {MERGEFIELD Sales}
{IF{MERGEFIELD State_Total}<> "" "
 The {MERGEFIELD State} total is: {MERGEFIELD State_Total}.
If you require more details about the above sales figures, please contact the {MERGEFIELD State} Sales Manager.
 
Sincerely,
 
Customer Service Manager (National)
{DATE \@ "dddd, d MMMM yyyy"}
"}"}
This is rather more extensive that what you appear to be using. The 'State' mergefield is the example's 'key' field. For your purposes, use the 'Customer' field. Similarly, where the tutorial uses 'State_Total', you'd use 'Total_Sales'. I'm sure you can figure the rest out.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 03-05-2012, 09:50 AM
daymaker daymaker is offline Windows XP Office 2000
Novice
 
Join Date: Feb 2012
Posts: 22
daymaker is on a distinguished road
Default

Paul, I'm back at it this morning...trying not to pull my hair out. I'm having trouble with a couple of things:

1. Getting all order information per company on the same list/page. It's creating a page per order.
2. I can't figure out how to format the fields so that the data merged in lines up under the column headings. The data is just all jammed together without the desired tabbing.

I attach the documents; maybe you can take another look and see if you can tell what I'm doing wrong? Note that I added a field of 'Item' to the spreadsheet.

John

EDIT: In addition to these fields, I want insert these additional fields before the repeated data: 'Address_1', 'Address_2' (most often will be blank), 'City', 'State', and 'Zip'. They are of course not in the attached documents; I attempted to build another MailMerge file and related spreadsheet this afternoon, but Word kept bombing on me...so I'm going to take a much-needed break from this for a bit and go have a short, healthy cry....


ACCO template_2.doc

Test dbase_2 0312.xls

ACCO template_2 merged.doc

Last edited by daymaker; 03-05-2012 at 02:51 PM.
Reply With Quote
  #6  
Old 03-05-2012, 05:40 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,294
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi daymaker,

You were nearly there. Aside from omitting one of the nested fields, you simply needed to add some tabs between your various table fields and do a bit of layout formatting to get the right result. In the attached, I found I also needed to change the table font to Arial Narrow to accomodate all the data.
Attached Files
File Type: doc ACCO template_2.doc (24.5 KB, 337 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 03-05-2012, 10:11 PM
daymaker daymaker is offline Windows XP Office 2000
Novice
 
Join Date: Feb 2012
Posts: 22
daymaker is on a distinguished road
Default

Thank You Sir for the tweaking! I added the address fields in with success. Now when I get into the office in the morning, I am going to try to transfer this programming over to my actual spreadsheet and give it a go. I also have to line everything up so that the address will print in the right spot for a window envelope. I'll post back on here as to how it goes...
Reply With Quote
  #8  
Old 03-13-2012, 12:44 PM
daymaker daymaker is offline Windows XP Office 2000
Novice
 
Join Date: Feb 2012
Posts: 22
daymaker is on a distinguished road
Default

Paul, just to let you know that my project turned out to be a success; thanks so much for your help.

One minor thing that still needs tweaking though is my zip code field. The merge process looked at my zipcode values in excel and actually subtracted the four digit from the five digit portion and output the difference. For instance, a nine-digit zipcode of 37744-0781 that was listed in the excel data source resulted in a zipcode of 36963 being printed on the letters. (We didn't realize this error until after all 898 letters were printed. Since we had to put a label on the outside of the envelopes for mailing anyway, this zipcode error was a mute point so we let it go anyway...). Any idea how I can prevent this in the future?
Reply With Quote
  #9  
Old 03-13-2012, 03:01 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,294
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi daymaker,

The anomoly is because zip codes with a '-' in them get treated as formulae. The following field code correctly formats 5-digit and 5+4-digit Zip Code data, even where the source data may have incorrect formatting such as a hyphen followed by trailing 0s after plain 5-digit Zip Codes or is formatted as a 5-digit or 9-digit string without hyphens. A data field named 'ZipCode' is assumed. Although I've laid the field code out on three lines for legibility, it can be be put on a single line.

{SET Zip {MERGEFIELD ZipCode}}
{IF{Zip}> 99999 {SET Zip {Zip \# "00000'-'0000"}}}
{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste them from this message. You also need to preserve the spaces where indicated.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #10  
Old 03-14-2012, 06:37 AM
daymaker daymaker is offline Windows XP Office 2000
Novice
 
Join Date: Feb 2012
Posts: 22
daymaker is on a distinguished road
Default

Paul, it worked.

Thanks once again -

John
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a consecutive list of dates in a MS word document whenever it is accessed CrabApple Word 11 05-17-2012 03:21 PM
How can I retreive multiple records using a lookup? jrpey Excel 3 11-14-2011 02:10 PM
Combine Records when Mail Merge from Access Tom Pickles Misc 2 08-24-2010 06:00 PM
Help with consolidating multiple records into one wbiggs2 Excel 0 11-30-2006 01:02 PM
Creating a Map From a List of Addresses aleccamp Excel 0 11-19-2005 03:04 PM


All times are GMT -7. The time now is 06:16 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft