MSWord - Access single data source problem

[Yair Sageev]

I'm trying to create a report template in Word that automatically extracts
data from an Access DB. The problem I'm having is that Word considers each
table in the database to be a different data source. Suppose table1 has a
primary key k1 and and k1 is a foreign key in table2. Word will not allow
me to select the data in t2 based on k1 because only one of the tables can
be a data source for the document at any one time.

Also, I have a table t3 that contains multiple records for each entry in t1,
keyed by k1. I would like to list both the records in t3 associated with
the record in t1, and also show the record in t2 based on k1. Word doesn't
seem to let me do this.

Can anyone help? Thanks.

 

[Albert D. Kallal]

You can build a query and join data into that query for lookup values.

So, just base your merge on a query in place of a table.

So, if you have 3 or 5 or 7 tables for lookup values, then just fire up the
query builder and drop in those additional tables (you just left joins for
this to work).

For the one to many situation, that is a different problem. You will have to
decide if you want to write some code to read in the data to some table
format, for some type of word automation. It really depends on how you want
to display the "many" side of the data.

Often, you can pre-process the data on the ms-access side into a temp table,
and then do the word merge on that.

So, between the use of some queries for the lookup and related values, you
can thus do the word merge on the query.

The one to many problem is different problem, and it really depends on what
kind of display, or how the data is to be used in word.

So, 1st part, or problem is easily solved with a query. The 2nd problem of
one to many ...hum, depends on what/how you want to display the data.

 

[Yair Sageev]

Thanks for the help.

I think you are right that for the 1-1 table relationships a giant join will
do the trick. As for the 1-many case, I would basically like to create a
full featured access report, except in Word.

For example:

Dear <<CustomerName>>

We value your business. Here is a well-formatted word document of all your
purchases for the current year:

<<order1>>
<<Product1>><<Quantity>><<PriceUnit>>
<<Product2>> ...
<<Total>>

<<order2>>
<<Product257>>
<<Product366>>
<<Product500>>
...

Thank you,

OurCompany

In Access this is simple. How to do it in Word?

It seems that the following product does what I am looking for:

http://www.4tops.com/wordlink.htm

I am *not* affiliated with the company. Has anyone tried this product?
Recommendations? Is it possible to do everything wordlink does in regular
mail merge?

 

[Albert D. Kallal]

I have no idea about that product. If you are looking for a way cool single
record word merge, then download my free example. It really does the job
well.

However, it will NOT work for what you need.

As for having "formatted" stuff to word, yes you could make the report and
then just use the "word button" in office to convert it to a word doc. That
would be a code free solution.

The other approach would be to simply have word open the data file, and
process the data.

I mean, if you were only to have one order, then you could certainly create
a temp table, write out the data to a memo field.

Thus, you simply create a record with fields like:

<<CustomerName>>

<<OrderData>>

Now, Order can be a field up to 255 characters with linefeeds in it.

You could also try using a memo field, but I don't know if more then 255
chars transfer in a word merge (just test this). If it allows more then 255
chars, then you are home free. Simply write out all the orders to the memo
field (formatted correctly), and then simply merge that. Use a fixed length
font, or even try using tabs in the data.

So, I would test a memo field.

If the memo field idea does not work, then you will have to write code on
the word side to "read" the mdb file.

My word merge sample can be found at:
http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html
(however, it DOES NOT work with linefeed data).

 

[Cindy Meister -WordMVP-]

Hi Yair,

As for the 1-many case, I would basically like to create a
full featured access report, except in Word.

There's a discussion about the methods available to you for
this on my website, in the "Special Merges" section of the
Mail merge FAQ, with links to sample files.

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

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