Results 1 to 3 of 3
  1. #1
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    590
    Thanks
    65
    Thanked 6 Times in 5 Posts

    Mail Merge from Two Tables (2003)

    This might be challenging. I have tblAllLocated, which contains information about our members. For several years I've used it as the data source for a Microsoft Word mail merge document that I print out every spring to use in fundraising. The mail merge document is quite intricate, with lots of IFs in various fields, carefully-adjusted field sizes, etc. After four years of polishing and bug-killing, it works very well with our data.

    This year I have a new table, tblDutyStations, which contains from one to ten former duty stations for each member for whom we have that information. In contains records for about 500 of our 1,300 members. The two tables are related through the Handle field; the Handle is similar to a customer number. tblDutyStations has just two fields: Handle and DutyStation. Multiple duty stations are handled by putting them into multiple records.

    I want to add duty stations to our annual printout this year, preferably in a 2 column X 5 row table at the bottom. This will require putting data from two tables into one mail merge document, possibly with some sort of intermediate step to combine the data into an Access query or Excel worksheet (but I'm just guessing about the feasibility of that).

    I'd like to keep things as a Word mail merge document if possible, but I COULD convert everything to an Access report. If I go to a report, I give up all the accumulated experience and bug-free nature of the well-proven mail merge document.

    Any suggestions?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: Mail Merge from Two Tables (2003)

    Option 1:

    You can create a query in the database based on tblAllLocated and tblDutyStations, joined on Handle. If *each* record in tblAllLocated has at least one corresponding record in tblDutyStations, you can use the standard join, but if there are some members without a corresponding record in tblDutyStations, you must double click the join line in the query design window and select the option to return *all* records from tblAllLocated.
    You can then use this query as data source for the merge. But since you now have multiple records per member, you must put Word through some hoops to prevent multiple letters per member. See MSKB article How to use mail merge to create a list sorted by category in Word.

    Option 2:

    You can create a query in the database based on tblAllLocated, and add a calculated field that concatenates the duty stations for a member.
    First, copy the Concat function attached to <post:=301,393>post 301,393</post:> into a standard module.
    Then, use the following expression in your query:

    DutyStations: Concat("tblDutyStations", "DutyStation", "Handle=" & [Handle])

    This assumes that Handle is numeric; if it is a text field, use

    DutyStations: Concat("tblDutyStations", "DutyStation", "Handle=" & Chr(34) & [Handle] & Chr(34))

    Add the fields from tblAllLocated. Save this query and use it as data source for the mail merge. You'll have an extra field DutyStations.

  4. #3
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    590
    Thanks
    65
    Thanked 6 Times in 5 Posts

    Re: Mail Merge from Two Tables (2003)

    Thanks! I'll experiment and let you know.

    I thought it might be impossible.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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