Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    78

    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
    52,816
    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 offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    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
    78
    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 offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    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
    52,816
    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 offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    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
    1,673
    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
    78
    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
    52,816
    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
    78
    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 offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    @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.

  13. #13
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    78
    @Moke123, First, I really like your design. It's very clean and simple.

    Am I not doing something right with your sample? When I open it, I immediately get an error message... "Run-time error '3078': The Microsoft Access Database engine could not find the input table or query 'tblMergeSource'. Make sure it exists and that its name is spelled correctly."

    I don't see anywhere in the VBA where tblMergeSource is created.

    Debug takes me to the EMPTY TABLE section of this code:

    Public Sub sAddPeople(strKeys As String)


    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strInsert As String


    strSql = "select * from tblPeople where PartyID in (" & strKeys & ")"


    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)


    If rs.BOF And rs.EOF Then
    GoTo MyExit
    End If


    'Empty the table
    db.Execute ("delete * from tblMergeSource"), dbFailOnError


    Do Until rs.EOF
    strInsert = "Insert into tblMergeSource(PartyID,FName,LName,DOB,Gender) " & _
    "values(" & rs!PartyID & ",""" & rs!FName & """,""" & rs!LName & """,#" & rs!DOB & "#,""" & rs!Gender & """)"


    db.Execute strInsert, dbFailOnError


    rs.MoveNext
    Loop


    MyExit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    End Sub

  14. #14
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I dont get any errors. Is the DB in a trusted location?

    When the main form loads it should run code that creates TempDB.accdb in the same folder as the main DB. It should also create a table in TempDB called tblMergeSource.

    Check and see if TempDB.accdb is being created and check if the table is being created.

    Edit: I should mention that the on close event of the main form deletes the temp table and temp database, so leave the main form open when you check for the temp database and table.
    Last edited by moke123; 06-30-2019 at 11:21 AM.

  15. #15
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    78
    It's on my desktop, which is set as a trusted location. TempDB is created, but tblMergeSource is not. I forgot to mention I'm also getting a message when I first open it, "ERROR 3067(QUERY INPUT MUST CONTAIN AT LEAST ONE TABLE OR QUERY.) IN PROCEDURE CREATE TABLE OF MODULE MODTEMPDB".

Page 1 of 2 12 LastLast
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
  •  
Other Forums: Microsoft Office Forums