Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting multiple members in one envelope (Access

    I have just been given this mail merge task. There are almost 10,000 individuals in tblMembers and about 6,000 families, so I REALLY don't want to have to do this by hand. Is there any solution that I can implement today so I can get these letters out and mailed by this afternoon. <sigh>

    I have a tblMembers with Keys of FamilyID and IndividualID and then their contact info, address, email, etc. I want to be able to send a single merged letter to everyone in the family, but that will have everyone's name in the salutation. For e.g. "Dear Gord, Lorraine, Graham and Kaylee" and then at a later point have an envelope that would be addressed to "Gord, Lorraine, Graham and Kaylee Holtslander".

    I think I want it to populate a tblMerge that would have a single record for each family ID. This record would hold the address info as well as the combined salutation and address info.

    I can conceptualize the method, I just don't have any idea how to make it happen in Access. So, I'd assume I'd want the system to
    Open tblMembers
    grab the familyid, individualid, and all the name and address info
    Write all of it to the tblMerge except the firstname
    In tblMembers, check the next familyID, if it's the same then grab that first name as well
    check the next familyID, if it's the same grab that first name, etc

    Once it gets to a new familyID it takes all the firstnames it has and writes them to the GatheredNames field in tblMerge with commas separating all of them except the last two which should be separated by "and"

    And then it carries on through the tblMembers until it reaches the end.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting multiple members in one envelope (Access

    If you're willing to live with a comma instead of "and" beween the next to last and last names, you could use the Concat function from <post#=301,393>post 301,393</post#>. You don't really have to create and populate a new table, you can use the function in a query and then use the query for the mail merge.

    If you really need the "and", we'd have to write custom code.

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting multiple members in one envelope (Acc

    Well, since I've been asked to do this in a single day, I think I'll just go with all commas all the time.

    I looked at the Concat function and I can follow the logic (I think). Where do I put this and how do I invoke it?

    Sorry to be such a newb, but I haven't gone under the hood in Access ever before today.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting multiple members in one envelope (Acc

    Activate the Modules section of the database window.
    Click New.
    Copy/paste the code from the post I referred to into the new module.
    Select Tools | References.. while still in the Visual Basic Editor.
    Make sure that the check box for Microsoft DAO 3.6 Object Library is ticked. (If it isn't ticked yet, you'll have to scroll down the list to find it), then click OK.
    Click the Save button on the toolbar, and provide a name for the module, for example basConcatenate.
    Switch back to Access.
    Activate the Queries section of the database window.
    Click 'Create Query in Design View'.
    Add tblMembers to the query, then close the Show Table dialog.
    Select View | Totals to change the query to a Totals query.
    Add the FamilyID field to the query grid, as well as the last name field and the address information. These should all have the default Group By in the Total line.
    Add a new calculated column:

    FirstNames: Concat("tblMembers","FirstName","FamilyID = " & [FamilyID])

    Substitute the correct table and field names if necessary.
    Set the Total option for this column to Expression.
    Save the query.
    If you prefer to create a table, you can change the query to a make-table query in the Query menu, then execute it.

    In your mail merge in word, you can use the FirstNames and LastName merge fields (with a space in between) in the address, and the word Dear followed by a space and the FirstNames merge field in the salutation.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting multiple members in one envelope (Access

    Gord

    It is probably to late for this mailing, if you repeat this procedure in the future you might want to consider using a window envelop that you fold your Access printed letter into. It beats running 6000 envelopes through a printer or a label print run.

    HTH, John

  6. #6
    New Lounger
    Join Date
    Oct 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting multiple members in one envelope (Acc

    Cool. 61 new bits of knowledge in one day. Thanks a LOT!!

  7. #7
    New Lounger
    Join Date
    Oct 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting multiple members in one envelope (Acc

    Hi Hans,

    I think I've done everything as you said, but when I run the query I get "Run-time error '3464: Data type mismatch in criteria expression.

    When I hit debug it drops me at this line in the code

    Set rst = dbs.OpenRecordset(strSQL)

    Unfortunately, after pressing the debug button, I'm not sure how to determine what exactly is at issue.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting multiple members in one envelope (Acc

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    New Lounger
    Join Date
    Oct 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting multiple members in one envelope (Acc

    Here's the stripped down db.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting multiple members in one envelope (Acc

    Do you have the same problem in the stripped down database? The query opens without problems when I try it (see below).

  11. #11
    New Lounger
    Join Date
    Oct 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting multiple members in one envelope (Acc

    Same thing in the stripped down one too.

    Well, thats more distressing than the really obvious mistake I was expecting.

    So, why would it work there and not here?

    Access 2003, SP2 the DAO 3.6 is turned on.

    My head hurts!

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting multiple members in one envelope (Acc

    I'm using Access 2002 SP-3, but I have no idea why it wouldn't work correctly in Access 2003 SP-2.
    Perhaps someone else with Access 2003 can test the query in the database you attached.

  13. #13
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting multiple members in one envelope (Acc

    I'm using Access 2003 SP2 and Access 2007 B2TR and it works fine in both for me. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  14. #14
    New Lounger
    Join Date
    Oct 2002
    Location
    Saskatoon, Saskatchewan, Canada
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting multiple members in one envelope (Acc

    OK, that's pretty weird. What can I do to try and get more information about what it's choking on in my machine?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting multiple members in one envelope (Acc

    Can you try your small sample databases on another PC in your organization?

Page 1 of 2 12 LastLast

Posting Permissions

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