Results 1 to 12 of 12

Repeating rows in Mail Merge

  1. #1
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    37

    Repeating rows in Mail Merge

    Hey, folks,



    I'm using Kallals word merge code (www.kallal.ca/wordmerge) and I've come across a problem that I should know the answer to. I have a table for my customers with their account number and basic demographic information. I have a table for account level information (a customer can have more than one account. These are identified by consecutive sequence numbers). I need to generate letters containing a table of the account level data (Sequence, date, type, owner, and balance). When I run my mail merge, I'm getting one letter per account, rather then one letter per customer with all account information. Could someone point me to the right resource for this? Apparently I'm not using the correct search terms to find what I need.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,165
    Why use mail merge instead of an Access report?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    260
    I use A.K.'s word merge in one of my apps but found that trying to get all the information into one query was too complicated and not easily edited as the app developed.
    What I do is create a temporary database with a temp table. I then use a series of append/update procedures to add the data to the temp table. For the merge I just use Select * from the temp table. The table is not normalized but then again it doesn't have to be.
    This allows me a lot of flexibility to add or delete fields to the temp table or to format data while appending.

  4. #4
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    37
    June7, The formatting would be very difficult and excessively time consuming. There's over 200 different existing word docs, each with slightly different layouts, different letterheads, different legal languages, etc. And moving them into a report would require Federal approval.

  5. #5
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    260
    When I run my mail merge, I'm getting one letter per account, rather then one letter per customer with all account information.
    Using the method I mentioned, You would use an append query to append each customer you wanted to the temp table. You would then use update procedures to add the other info for each customer. One record, one customer. The data need not be normalized as all you need is to have all the merge fields in one record. You can have up to 255 fields in a table therefore 255 mergefields.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,165
    So, with moke's idea, code could concatenate data into a single string for each detail record. So each detail row would occupy 1 field in table. Or concatenate all detail records into 1 very long string using embedded CrLf to force line breaks and save in a memo field. However, seems I've read that mail merge has issue with strings longer than 255 characters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    260
    However, seems I've read that mail merge has issue with strings longer than 255 characters.
    I can't recall ever having a field in excess of 255 characters so I cant confirm or refute that but since the temp table is not normalized there is a lot of wiggle room for work arounds.

    Here's an example I did a while ago. AKWordDemoLib.zip

    Note the Db will add a folder named DocLibrary in the root folder and subfolders within that for the libraries you add. This allows you to sort the
    documents into personalized subfolders. I didnt include any documents so you'll need to create a library, start the merge and create and save a document.

    HTH

  8. #8
    Join Date
    Apr 2017
    Posts
    885
    It looks like you really have as MM source a query (either created by you, or by 3rd party (Kallal's) app) like
    Code:
    SELECT cust.CustomerID, cust.CustomerName, ... , acc.CustomerAccountNr, acc.AcountNo, ... FROM tblCustomers cust LEFT OUTER JOIN tblAccounts acc ON acc.CustomerID = cust.CustomerID
    The problem is, such query returns several records for customer, when customer has several accounts. And MM greates always a document per record!

    You need for this document a separate saved query, like
    Code:
    SELECT 
              cust.CustomerID, cust.CustomerName, ... , 
              acc1.CustomerAccountNr As AccountNr1, acc1.AccountNo As AccountNo1, ..., 
              acc2.CustomerAccountNr As AccountNr2, acc2.AccountNo As AccountNo2, ...,
              acc3.CustomerAccountNr As AccountNr3, acc3.AccountNo As AccountNo3, ...,
              ...
    FROM (((tblCustomers cust LEFT OUTER JOIN tblAccounts acc1 ON acc1.CustomerID = cust.CustomerID) 
              LEFT OUTER JOIN tblAccounts acc2 ON acc2.CustomerID = cust.CustomerID)
              LEFT OUTER JOIN tblAccounts acc3 ON acc2.CustomerID = cust.CustomerID)
              ...
    WHERE acc1.CustomerAccountNr = 1 AND acc2.CustomerAccountNr= 2 AND acc3.CustomerAccountNr= 3 ...

  9. #9
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    37
    Thank you all for your input. I'm going to keep this open while I try a couple of your suggestions. It seems I might be able to get my letters into access reports without having to go through the Feds. The problem is the time it'll take to create the volume of report layouts. Does anyone know of a simple way to covert them, maintaining spacing alignment, etc?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,165
    Convert from what to what? Word doc to Access report? No, there is no simple way to convert, as you already acknowledged. You can copy/paste strings of text into textboxes.

    Depending on how different these reports are, conditional coding in VBA and textboxes might be able to deal with variations so maybe you won't need 200 separate report objects.

    I have one report where I use a jpg image of a document with blank spaces where data fits. Then textboxes overlay the image to feed data. But my document is a form with boxes where data would be written in by hand, not a letter with paragraphing and variable length, and no subdataset of related records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    37
    Thanks, June7! I've been playing around with conditional paragraphs over the last few weeks, so that's a possibility. I really appreciate your time and knowledge!

  12. #12
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    260
    @June - I just tested my method with a memo field in excess of a 1000 characters and it appears to merge okay although I cant say if there wouldn't be unexpected errors without extensive testing.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 22
    Last Post: 12-20-2015, 02:46 AM
  2. Replies: 1
    Last Post: 04-14-2014, 12:29 PM
  3. Repeating rows in a query
    By excellenthelp in forum Access
    Replies: 1
    Last Post: 03-27-2014, 01:57 PM
  4. repeating rows
    By slimjen in forum Forms
    Replies: 1
    Last Post: 11-20-2013, 03:26 PM
  5. Replies: 7
    Last Post: 10-30-2012, 02:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums