Merging with Access queries - Multiple

[CPutnam]

Hi, all. I'm attempting to create a merge document that draws its'
information from multiple Access queries. The data that I need to include
in the document comes from multiple tables. I've created 3 queries -- one
combines information from about 6 tables and the other two just have
information from 1 table each. I split the queries out this way to try and
get around the problem with relational databases that if there isn't any
information in one of the tables in a query then the whole query returns
nothing. (And if I try to do Right Joins to get around this problem, Access
gets really mixed up.)

I've found out (through reading this newsgroup and others) that each merge
document can only have one data source. I've also found out that Master
documents are too buggy to count on (especially since the merge document
will be distributed to multiple users in many different locations). I also
tried to create other merge documents for using the two other queries as
their data sources and then use {INCLUDETEXT} to bring in information from
those documents but the "master" merge document didn't want to use any
fields that weren't in the main data source.

I could do multiple merges into different documents and than have the users
copy and paste into the "master" merge document but I can already hear the
protests!

Does anyone have any ideas at all that might help me? Are there
alternatives to using Master Documents? Thanks in advance. Carol.

 

[Doug Robbins]

It's not really clear exactly what is the cause of your problem, but one
thing I can tell you is that Access is far better at manipulating data than
is Word. Without knowing the details it's hard to give any specific advice.
--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[CPutnam]

I'm not sure what specifics you need but I'll try:

My document is a report that draws information from a table that has a
project name and general information about a project. But another table
contains information about who is involved with the project (the Contacts).
It is in this secondary table because there could be many Contacts for the
one Project. Sometimes (like at the start of a project) there may be NO
data in the secondary Contacts table. So a query in Access wouldn't have
any results and would result in a blank Word document (except for the
headings) after a merge.

I am trying to make sure that if there is data in the Project table but NO
data in the Contacts table, there would still be General Project
information in my final merged document. I have made the Projects table in
Access my merge data source which works fine. But the only way I can figure
out to try and include the information from the Contacts table (if it
exists) is to create ANOTHER merged document that just has the Contacts
information and then use an {INCLUDEText} field to include the information
from the Contacts merged document in the first document with the Projects
info. But Word won't let me do this and says that it can't find the fields
from the Contacts in the merge data source (which is from the Proejcts
table).

In Access, I have been able to create a report that contains all the data I
need but when I convert it to Word, it becomes an RTF file and I need it in
Word format (besides all the formatting clean-up that has to be done in an
RTF file). Maybe I should pose this question to an Access group. What do
you think?

Thanks for any ideas. Carol.

 

[Doug Robbins]

Well, I would do it all in Access. It's report formatting capabilities are
quite good so I don't understand the need to bring it into Word, or, if you
do, why there is so much formatting to be done.

For mailmerge, Word can really only use a "flat file" as the data source.
Using visual basic, there are ways to get around this, so if you are up to
that, you can learn a bit from the "Multiple items per condition" item under
the "Special merges" section of fellow MVP CIndy Meister's website at:

http://homepage.swissonline.ch/cindymeister/MergFram.htm

and my looking at another MVP, Albert Kallal's website:

http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[CPutnam]

Thanks, Doug. I've already produced the report in Access but have been told
that exporting it to Word (actually .RTF format) isn't good enough (because
of the loss of formatting, fonts, etc.). But I've also been told that it
needs to be in Word format because it has to be included with lots of other
stuff for a board meeting.

I'm already familiar with Cindy's and Albert's sites (even have them
bookmarked) but I'll look at them again.

Thanks. Carol.

 

[Doug Robbins]

Hi Carol,

There is no reason that you should lose any formatting when sending an
Access Report to an .RTF file.

On rare occassions on very complex reports, I have noticed minor differences
in character spacing that may cause a "line wrap" to occur that necessitated
going back to Access and making a slight adjustment to the width of the
offending control on the report.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP