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

[Guest]

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

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

Thanks and wishing you a good day

Stefan van den Hark
The Netherlands

 

[John Nurick]

Hi Stefan,

Responses inline.

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

 

[Guest]

Thanks John,

I am going to look at several options, right now I try to
do it with automation. But it is not as easy as the books
do make it sounds

thanks a great deal. will let you know the outcome

stefan

-----Original Message-----
Hi Stefan,

Responses inline.

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#Hook om,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.
.

 

[John Nurick]

I am going to look at several options, right now I try to
do it with automation. But it is not as easy as the books
do make it sounds

Most of the books don't even mention this bit!

Good luck, and post back here or in one of the Word newsgroups when you
have specific questions.

 

[Guest]

hi John,

Thanks for all this info, I am impressed about the power of these newsgroup
really amazing.

I tried the lebans site. I unfortunately do not manage to install the tool
properly. I am working with access and word 2000, while the report utility
seems to be developed under acces 97. could that be a problem?

I can not convert the database to 2000, it is not accepted.

I can open it but then I am not allowed to change/edit anything. The reports
that are included, I can not open either. So I am stuck here.

would you know a way out?

Thanks and kind regards,

stefan

 

[John Nurick]

I tried the lebans site. I unfortunately do not manage to install the tool
properly. I am working with access and word 2000, while the report utility
seems to be developed under acces 97. could that be a problem?

http://www.lebans.com/ReportUtilities.htm has separate versions of the
report utilities for Access 97, Access 2000 and Access 2002. Are you
certain that you downloaded the right one?