Re: mailmerging multiple tables from access in one word-document: how?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: John Nurick (j.mapSoN.nurick_at_dial.pipex.com)
Date: 12/12/04 

Date: Sun, 12 Dec 2004 10:30:14 +0000

Hi Stefan,

Responses inline.

On Sat, 11 Dec 2004 13:59:01 -0800, "svdh"
<svdh@discussions.microsoft.com> wrote:

>I have been looking at a problem the entire week in access, I have still not
>been able to find a solution. Hope that you could maybe tell where to look
>
>Concerns the link between Access and Word. I can not transfer a report to
>word without losing out on the lay-out (RTF format). I understand that there
>is no way out

See http://www.lebans.com/ReportUtilities.htm

If what you need is a well-structured Word document (i.e. making
consistent use of styles) rather than a well-formatted one, this is an
approach that I have been intending to investigate:

1) Install the Windows Generic/Text Only printer driver.
2) Set up the report (and subreports if any) without page or character
formatting, but with XML tags corresponding to your Word styles. Most of
the tags would be inserted by including them in calculated fields, but
they could also be included in labels.
3) Print the report to a file.
4) Import the XML into Word.

>ok, mail merge I thought. But here I have the problem that I need to merge
>multiple tables and that I can just include one in the mailmerge within word.
>A query would seem likely but there I have a problem. I have one master table
>containing personal information on staff and 8 subtables around the master
>table with specifying language, workexp, education for each person. The
>number of entries in the subtables varies per person.
>
>In case I use a query to combine all these tables I get # x # x # x # x# (9
>times) number of records an extreme number of records for each person.
>
>An example: person A
>knows 8 languages
>has 3 educational references
>10 publications
>took 8 courses in his career
>
>This would already result in 1 X 8 X 10 X 8 = 640 records
>
>Do I miss a certain function with queries or how can I work this out? I can
>not imagine that it is not possible

There's no really elegant way of doing this because Word mailmerge
doesn't allow for an equivalent of sub-queries and sub-reports.
Depending on the needs, you might be able to use a custom VBA function
in your query to (for example) pull concatenate 8 languages into a
single string returned by your query. There are concatenate functions at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane and
http://www.mvps.org/access

Otherwise, it's a matter of writing VBA code that uses Automation to
control Word and create the document just as you need it. See e.g.

ACC: Sending the Current Record to Word with Automation
http://support.microsoft.com/?id=209976

Access a database and insert into a Word document the data that you find
there: http://www.word.mvps.org/faqs/interdev/GetDataFromDB.htm

ACC: Using Automation to Create and Manipulate an Excel Workbook
http://support.microsoft.com/?id=142476 

--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.

Relevant Pages

  • Re: mailmerging multiple tables from access in one word-document: how?
    ... do it with automation. ... >2) Set up the report without page ... >>A query would seem likely but there I have a problem. ... >>knows 8 languages ...
    (microsoft.public.access.externaldata)
  • Re: Merging data from Access Query
    ... merged into a word document, I get the SQL message about merging data into ... I have re-established the links from the query to the table fields, ... the SQL information inside of word to make sure everything is right for the ... to merge the fields from the access database query, ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Email Reports
    ... Use automation to open the Word document and set the recordsource to ... Once you have either a merged Word document or a distinct PDF file, ... > Set objWordDoc = objWordApp.Documents.Open ...
    (microsoft.public.access.reports)
  • RE: Running action function from SELECT query
    ... the results of the query, and passing those results back to the web. ... If you're executing the statement via DAO ... My app already uses an API to interface with a credit card processor. ... server and then opening it using automation to call and execute the API. ...
    (microsoft.public.access.modulesdaovba)
  • Scripting 101
    ... automation environment and it allows me to quickly create software to ... although somewhat primitive compared to scripting languages ... However, unlike most programming languages of the time, these scripts ...
    (comp.home.automation)