Embed tables from Access dynamically into Word mail merge letters?

[Thomas Staudte]

Hi NG,

I have two Access tables, one containing customer names, the other one items
each customer has purchased. The tables are linked properly with an ID. The
task is now that a mail merge letters shall be sent to each customer that
includes a table of all item the customer has purchased.

Mail merge letters, Access queries, etc. are no problem, but how do I create
such a linked table in a Word mail merge letter? Is that possible at all?

Thank you for any hints.

Thomas Staudte

 

[Doug Robbins - Word MVP]

Why not create the letter as an Access Report.

Word does not really have the ability to perform a "multiple items per
condition (=key field)" mailmerge.
See the "Group Multiple items for a single condition" item on fellow MVP
Cindy Meister's website at

http://homepage.swissonline.ch/cindymeister/mergfaq1.htm#DBPic


Or take a look at the following Knowledge Base Articles

http://support.microsoft.com/default.aspx?scid=kb;en-us;302665


http://support.microsoft.com/default.aspx?scid=kb;en-us;294686

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Andrea Jones]

Have a look at http://www.allaboutoffice.co.uk/worddatabase.htm , you can use
a database field to create your table of related items (pick any account
field to start with and change it later to link to your MERGEFIELD). If you
look at the bottom of the page I have directed you to there are instructions
about how to link the table created using the database field to records in
your mailmerge data source.

Andrea Jones
www.allaboutoffice.co.uk
www.allaboutclait.com

 

[Doug Robbins - Word MVP]

It would really help Joe AverageUser if the article to which you made
reference gave an example of just where you would insert the { MERGEFIELD
Player } into

"SELECT Date, Player, Goals FROM C:\\Documents and Settings\\Allabout\\My
Documents\\Excel\\goals.xls WHERE ((Player = 'Henry')) OR ((Player =
'Nistelrooy')) ORDER BY Player, Date"

 

[Thomas Staudte]

Hi Andrea and Doug,

thanks a lot for the pointer, I'm pretty sure I'll get this to work from
here!

Best regards and greetings from Germany

Thomas Staudte

 

[Andrea Jones]

Thomas

I forgot to say, press CTRL+F9 to get the curly brackets for the Mergefield
field code. If you press ALT+F9 you'll see your merge field codes and can
copy the relevant one exactly into the select statement. Don't forget to
leave the apostrophes around the field code in the WHERE part or it won't
work.

Andrea Jones
www.allaboutoffice.co.uk

 

[Doug Robbins - Word MVP]

That's still not clear.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Thomas Staudte]

Hi Doug,

thanks for assisting, but Andrea's tips were perfectly clear to me. The only
pointer I needed was to the Database field. But just in case the Joe Averageuser
reads this thread and is confused, here's the field I used:

{ DATABASE \d "F:\\test.mdb" \c "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User
ID=Admin;Data Source=F:\\test.mdb;Mode=Read;Extended Properties=\"\";Jet
OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDBatabase
Password=\"\";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=0;Jet
OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet
OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" \s "SELECT
`Value` FROM `Invoices` WHERE ((`Name` = '{ MERGEFIELD "Name" }'))" \h }

The field was inserted and configured using Insert->Field->Database. I first
configured the filter with some sample code to have a basis for the Select
statement (i.e. "SELECT `Wert` FROM `Werte` WHERE ((`Name` = 'Smith'))" )
and then replaced the Smith with { MERGEFIELD "Name" }, which is a field
from the main merge file. Actually pretty simply, but the silly backquotes
don't really make life easy ;-)

Agains, thanks to both of you for setting me on the right tracks.

Thomas Staudte

 

[Doug Robbins - Word MVP]

I'm glad that you got it worked out. I am not sure that Joe Average User
would have. An example would make the article to which Andrea referred much
clearer to the Average Joe.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP