Mail Merge with DB to create a table of related records

[MMbosman]

I have a list of divisions that have vehicles assigned in each division. I
need to create a mail merge in Word 2003 that creates a single document per
division with a listing of each vehicle in the division. The number of
vehicles changes based on the division.

The mail merge is not a problem, but how can I link a database query to
populate a table to the mail merge field for each division? This is giving me
fits, but there must be an easy way to do this that I am overlooking.

 

[Peter Jamieson]

Word isn't particularly well set up to do this (if possible, use another
reporting tool that is, such as Access reporting), but what you do is
a. use the division list as the data source for the merge
b. enable the Database toolbar in Word (e.g. Tools|Customize)
c. use that to insert the databse of vehicles /as a field/ you then have
something like

{ DATABASE \d "your database file, if it's a .mdb, or a .odc file, if it's a
SQL Server table"
\c "a whole bunch of connection information"
\s "SELECT * FROM [Vehicles]" \h }

Use Alt-F9 to look at the field code, and manually alter the SELECT field so
it coordinates with the Division, e.g. if you have a numeric DivisionID as a
foeign key in your Vehicles table, and your Division table also has a field
"DivisionID" field, you will need something like

\s "SELECT " FROM [Vehicles] WHERE DivisionID = { MERGEFIELD DivisionID }"
\h

If the DivisionID is alphanumeric, you would need something more like

\s "SELECT " FROM [Vehicles] WHERE DivisionID = '{ MERGEFIELD DivisionID }'"
\h

/All/ the {} need to be the special field codes you can insert using
ctrl-F9. You may also find that Word does not recognise apparently correct
SQL syntax and requires you to alias the table name, e.g.

\s "SELECT v." FROM [Vehicles] v WHERE v.DivisionID = { MERGEFIELD
DivisionID }" \h

Peter Jamieson

 

[MMbosman]

First off thanks for the speedy reply. I've attempted to make this work, but
without success. My data source in this instance is an excel sheet. I can
edit the query and enter a number for the division and that is successful but
when using the MERGEFIELD as the link between the merge and the database it
isn't working. I'll keep fiddling. Thanks again.

Word isn't particularly well set up to do this (if possible, use another
reporting tool that is, such as Access reporting), but what you do is
a. use the division list as the data source for the merge
b. enable the Database toolbar in Word (e.g. Tools|Customize)
c. use that to insert the databse of vehicles /as a field/ you then have
something like

{ DATABASE \d "your database file, if it's a .mdb, or a .odc file, if it's a
SQL Server table"
\c "a whole bunch of connection information"
\s "SELECT * FROM [Vehicles]" \h }

Use Alt-F9 to look at the field code, and manually alter the SELECT field so
it coordinates with the Division, e.g. if you have a numeric DivisionID as a
foeign key in your Vehicles table, and your Division table also has a field
"DivisionID" field, you will need something like

\s "SELECT " FROM [Vehicles] WHERE DivisionID = { MERGEFIELD DivisionID }"
\h

If the DivisionID is alphanumeric, you would need something more like

\s "SELECT " FROM [Vehicles] WHERE DivisionID = '{ MERGEFIELD DivisionID }'"
\h

/All/ the {} need to be the special field codes you can insert using
ctrl-F9. You may also find that Word does not recognise apparently correct
SQL syntax and requires you to alias the table name, e.g.

\s "SELECT v." FROM [Vehicles] v WHERE v.DivisionID = { MERGEFIELD
DivisionID }" \h

Peter Jamieson

I have a list of divisions that have vehicles assigned in each division. I
need to create a mail merge in Word 2003 that creates a single document
per
division with a listing of each vehicle in the division. The number of
vehicles changes based on the division.

The mail merge is not a problem, but how can I link a database query to
populate a table to the mail merge field for each division? This is giving
me
fits, but there must be an easy way to do this that I am overlooking.

 

[Peter Jamieson]

My best guess in this case is that you are seeing error messages because
your data source is already open and you are trying to use the same .xls as
the source for the DATABASE field. But I can't be sure. What error message
(or other evidence of failure) are you seeing?

Peter Jamieson

First off thanks for the speedy reply. I've attempted to make this work,
but
without success. My data source in this instance is an excel sheet. I can
edit the query and enter a number for the division and that is successful
but
when using the MERGEFIELD as the link between the merge and the database
it
isn't working. I'll keep fiddling. Thanks again.

Word isn't particularly well set up to do this (if possible, use another
reporting tool that is, such as Access reporting), but what you do is
a. use the division list as the data source for the merge
b. enable the Database toolbar in Word (e.g. Tools|Customize)
c. use that to insert the databse of vehicles /as a field/ you then have
something like

{ DATABASE \d "your database file, if it's a .mdb, or a .odc file, if
it's a
SQL Server table"
\c "a whole bunch of connection information"
\s "SELECT * FROM [Vehicles]" \h }

Use Alt-F9 to look at the field code, and manually alter the SELECT field
so
it coordinates with the Division, e.g. if you have a numeric DivisionID
as a
foeign key in your Vehicles table, and your Division table also has a
field
"DivisionID" field, you will need something like

\s "SELECT " FROM [Vehicles] WHERE DivisionID = { MERGEFIELD
DivisionID }"
\h

If the DivisionID is alphanumeric, you would need something more like

\s "SELECT " FROM [Vehicles] WHERE DivisionID = '{ MERGEFIELD
DivisionID }'"
\h

/All/ the {} need to be the special field codes you can insert using
ctrl-F9. You may also find that Word does not recognise apparently
correct
SQL syntax and requires you to alias the table name, e.g.

\s "SELECT v." FROM [Vehicles] v WHERE v.DivisionID = { MERGEFIELD
DivisionID }" \h

Peter Jamieson

I have a list of divisions that have vehicles assigned in each division.
I
need to create a mail merge in Word 2003 that creates a single document
per
division with a listing of each vehicle in the division. The number of
vehicles changes based on the division.

The mail merge is not a problem, but how can I link a database query to
populate a table to the mail merge field for each division? This is
giving
me
fits, but there must be an easy way to do this that I am overlooking.

 

[MMbosman]

Actually I didn't fully qualify the sheet name with `Sheet1$`.Division that
was my problem. Thanks for your help, but I have one last question.

Now that it is hooked up to each data source is it possible to set something
to force the table filled by the database to refresh itself without having to
press the refresh button - sort of the same way a report and sub report work
in access?

My best guess in this case is that you are seeing error messages because
your data source is already open and you are trying to use the same .xls as
the source for the DATABASE field. But I can't be sure. What error message
(or other evidence of failure) are you seeing?

Peter Jamieson

First off thanks for the speedy reply. I've attempted to make this work,
but
without success. My data source in this instance is an excel sheet. I can
edit the query and enter a number for the division and that is successful
but
when using the MERGEFIELD as the link between the merge and the database
it
isn't working. I'll keep fiddling. Thanks again.

Word isn't particularly well set up to do this (if possible, use another
reporting tool that is, such as Access reporting), but what you do is
a. use the division list as the data source for the merge
b. enable the Database toolbar in Word (e.g. Tools|Customize)
c. use that to insert the databse of vehicles /as a field/ you then have
something like

{ DATABASE \d "your database file, if it's a .mdb, or a .odc file, if
it's a
SQL Server table"
\c "a whole bunch of connection information"
\s "SELECT * FROM [Vehicles]" \h }

Use Alt-F9 to look at the field code, and manually alter the SELECT field
so
it coordinates with the Division, e.g. if you have a numeric DivisionID
as a
foeign key in your Vehicles table, and your Division table also has a
field
"DivisionID" field, you will need something like

\s "SELECT " FROM [Vehicles] WHERE DivisionID = { MERGEFIELD
DivisionID }"
\h

If the DivisionID is alphanumeric, you would need something more like

\s "SELECT " FROM [Vehicles] WHERE DivisionID = '{ MERGEFIELD
DivisionID }'"
\h

/All/ the {} need to be the special field codes you can insert using
ctrl-F9. You may also find that Word does not recognise apparently
correct
SQL syntax and requires you to alias the table name, e.g.

\s "SELECT v." FROM [Vehicles] v WHERE v.DivisionID = { MERGEFIELD
DivisionID }" \h

Peter Jamieson

I have a list of divisions that have vehicles assigned in each division.
I
need to create a mail merge in Word 2003 that creates a single document
per
division with a listing of each vehicle in the division. The number of
vehicles changes based on the division.

The mail merge is not a problem, but how can I link a database query to
populate a table to the mail merge field for each division? This is
giving
me
fits, but there must be an easy way to do this that I am overlooking.