Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merge One to Many from Access (97 Sr2)

    I have an invoice that I want to create using Merge. It is an invoice to landlords for the properties they manage. I have a list of these properties in an Access query. And the list of landlords is in a separate Access query. I want to know how to have a letter addressed to ONE landlord on page one, and a list of that landlord's properties on page two (the list coming from Access). I know how to do a mail merge with one landlord and one property, but how do I get the second page to list all the properties and not repeat the landlord?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Merge One to Many from Access (97 Sr2)

    The challenge in trying to do this is that a Word merge can only have one data source, be it a Word document, spreadsheet, or Access table or query. That being the case, you need to construct a query, presumably a parameter query, which has all the landlord data, and all the property info in a recordset. Then in Word you use the {next record} field to step through all the property records about a specific landlord. In principle, this approach should work - you are essentially using the catalog or mailing labels type of merge. The complication that arises is that Word isn't very good about knowing what to do when you exceed the page length on the second page.

    Another approach would be to use OLE Automation, and drive the whole process from Access. In that scenario, you would be able to use two different recordsets, one for the landlord, and another for the properties. It does involve a fair bit of VBA however - if you are interested there is a good treatment of how to do that kind of thing in the Access Developers Handbook. On the other hand, you might find it just as easy to do the entire invoice in Access - it's pretty good at these kind of things. We used to do invoices and other forms as Word merges, but finally converted them to Access reports, and they work pretty well. Hope this gives you some guidance.
    Wendell

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge One to Many from Access (97 Sr2)

    Hi Melanie:
    To see an example of what Wendell describes in his first paragraph, see this site.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge One to Many from Access (97 Sr2)

    For an article on Automation have a look at the Word Automation Part II article here.

    It may give you some ideas on how to tackle it.

    The intended audience for it are Access Developers, but it should be readable and understandable by anyone that knows VBA. But then I am a little biased. I'm the author of it.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

Posting Permissions

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