Access and Word Merging

[Greg]

Hi All,
I have an Access database that is used as a front end for
SQL Server.
Users enter data via Access and use the reporting engine
in Access to develop and print reports.
The challenge is to design something better than what is
available in Access to export a report to Word. Access
stores the data in 10+ tables and the report utilizes
grouping and sorting features. That is all OK.
However, when you export the report to Word, the report
contains many, many tabs (used to format report in
Access).
The report runs to 250 pages, so deleting each tab is not
appropriate.
I have thought of using the mail merge feature in Word to
pull the data from Access and then use the merge feature
to format the report in Word.
Can you group and sort data in Word?
Is there an alternative? (Excel is no good as the length
of a number of the fields is greater than 255 characters)
what do you recommend?
Thanks - all suggestions gratefully received!

 

[Cindy M -WordMVP-]

Hi Greg,

Since you want to use grouped data, I'd say mail merge is
definitely NOT the way to go. Instead, I'd say automate Word
and pass the data into bookmarks (instead of merge fields).
Use DAO to access the recordsets, and use the SQL
(QueryDefs) to sort the data.

I generally concatenate data groups using the VBA code, then
dump them as delimited strings into the Word document and
complete the formatting there.

In the Special Merges section of the mail merge FAQ on my
website there's a discussion about one-to-many into Word. As
part of that, I have a link to a ZIP file WdAcc97.zip. This
contains some examples for passing Access data into Word.
Some of the code isn't pretty , but it should give you a
starting point.

I have an Access database that is used as a front end for
SQL Server.
Users enter data via Access and use the reporting engine
in Access to develop and print reports.
The challenge is to design something better than what is
available in Access to export a report to Word. Access
stores the data in 10+ tables and the report utilizes
grouping and sorting features. That is all OK.
However, when you export the report to Word, the report
contains many, many tabs (used to format report in
Access).
The report runs to 250 pages, so deleting each tab is not
appropriate.
I have thought of using the mail merge feature in Word to
pull the data from Access and then use the merge feature
to format the report in Word.
Can you group and sort data in Word?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail