Merging from two sources

[Richard]

Despite all that I have read on here I know this is possible - I have
succesfully done so in the past and the article "Mail Merge: Part II" located
at: http://msdn2.microsoft.com/en-us/library/aa140197(office.10).aspx
explains how - but the article is for Word 2000, and I am either missing
something, or the process is different for 2003.

I have two tables, the first contains the names and email addresses of
contacts. The second contains details about the contacts - one to many
relationship. I am attempting to use mail merge for the information from the
first table, and the DATABASE FIELD command to insert a table containing the
information in the second table, everything works - except when I try to
filter the second one to only show the records relevant to the officer - my
text is below what am I missing?


{DATABASE \d "C:\\Documents and Settings\\p7psri\\Desktop\\Book1.xls" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User ID=Admin;Data
Source=C:\\Documents and
Settings\\p7psri\\Desktop\\Book1.xls;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet
OLEDB:Registry Path=\"\";Jet OLEDBatabase Password=\"\";Jet OLEDB:Engine
Type=35;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 `entryid`,
`number`, `item` FROM `Sheet2$` WHERE ((`number` = '{MERGEFIELD number}'))"
\h }

This has really stumped me especially since I know that this is possible any
help would be greatly appreceiated.

Thank you

 

[Peter Jamieson]

Try the following:
a. ensure that { MERGEFIELD number } is a field, i.e that the {} are the
special field braces you can insert using ctrl-F9
b. if "number" is actually numeric, try losing the quaotes around {
MERGEFIELD number }
c. add a table alias and qualify all the filed names (you shouldn't have to
do this but for some reason Word+Jet OLE DB seem to insist on it).

e.g.

{DATABASE \d "C:\\Documents and Settings\\p7psri\\Desktop\\Book1.xls" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User ID=Admin;Data
Source=C:\\Documents and
Settings\\p7psri\\Desktop\\Book1.xls;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet
OLEDB:Registry Path=\"\";Jet OLEDBatabase Password=\"\";Jet OLEDB:Engine
Type=35;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 s.entryid,
s.number, s.item FROM `Sheet2$` WHERE ((s.number = '{MERGEFIELD
number}'))"
\h }

or

{DATABASE \d "C:\\Documents and Settings\\p7psri\\Desktop\\Book1.xls" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User ID=Admin;Data
Source=C:\\Documents and
Settings\\p7psri\\Desktop\\Book1.xls;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet
OLEDB:Registry Path=\"\";Jet OLEDBatabase Password=\"\";Jet OLEDB:Engine
Type=35;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 s.entryid,
s.number, s.item FROM `Sheet2$` WHERE ((s.number = {MERGEFIELD
number}))"
\h }

You can probably simplify that to something more like

{DATABASE \d "C:\\Documents and Settings\\p7psri\\Desktop\\Book1.xls" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Data
Source=C:\\Documents and
Settings\\p7psri\\Desktop\\Book1.xls;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:Engine
Type=35;" \s "SELECT s.entryid,
s.number, s.item FROM `Sheet2$` WHERE (s.number = {MERGEFIELD number})"
\h }

and probably even more than that.

 

[Richard]

Thank you - the CTRL - F9 was a great help - I mucked around with it and got
the syntax to work.

Try the following:
a. ensure that { MERGEFIELD number } is a field, i.e that the {} are the
special field braces you can insert using ctrl-F9
b. if "number" is actually numeric, try losing the quaotes around {
MERGEFIELD number }
c. add a table alias and qualify all the filed names (you shouldn't have to
do this but for some reason Word+Jet OLE DB seem to insist on it).

e.g.

{DATABASE \d "C:\\Documents and Settings\\p7psri\\Desktop\\Book1.xls" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User ID=Admin;Data
Source=C:\\Documents and
Settings\\p7psri\\Desktop\\Book1.xls;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet
OLEDB:Registry Path=\"\";Jet OLEDBatabase Password=\"\";Jet OLEDB:Engine
Type=35;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 s.entryid,
s.number, s.item FROM `Sheet2$` WHERE ((s.number = '{MERGEFIELD
number}'))"
\h }

or

{DATABASE \d "C:\\Documents and Settings\\p7psri\\Desktop\\Book1.xls" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User ID=Admin;Data
Source=C:\\Documents and
Settings\\p7psri\\Desktop\\Book1.xls;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet
OLEDB:Registry Path=\"\";Jet OLEDBatabase Password=\"\";Jet OLEDB:Engine
Type=35;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 s.entryid,
s.number, s.item FROM `Sheet2$` WHERE ((s.number = {MERGEFIELD
number}))"
\h }

You can probably simplify that to something more like

{DATABASE \d "C:\\Documents and Settings\\p7psri\\Desktop\\Book1.xls" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Data
Source=C:\\Documents and
Settings\\p7psri\\Desktop\\Book1.xls;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:Engine
Type=35;" \s "SELECT s.entryid,
s.number, s.item FROM `Sheet2$` WHERE (s.number = {MERGEFIELD number})"
\h }

and probably even more than that.

--
Peter Jamieson
http://tips.pjmsn.me.uk

Despite all that I have read on here I know this is possible - I have
succesfully done so in the past and the article "Mail Merge: Part II"
located
at: http://msdn2.microsoft.com/en-us/library/aa140197(office.10).aspx
explains how - but the article is for Word 2000, and I am either missing
something, or the process is different for 2003.

I have two tables, the first contains the names and email addresses of
contacts. The second contains details about the contacts - one to many
relationship. I am attempting to use mail merge for the information from
the
first table, and the DATABASE FIELD command to insert a table containing
the
information in the second table, everything works - except when I try to
filter the second one to only show the records relevant to the officer -
my
text is below what am I missing?


{DATABASE \d "C:\\Documents and Settings\\p7psri\\Desktop\\Book1.xls" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User ID=Admin;Data
Source=C:\\Documents and
Settings\\p7psri\\Desktop\\Book1.xls;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet
OLEDB:Registry Path=\"\";Jet OLEDBatabase Password=\"\";Jet OLEDB:Engine
Type=35;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 `entryid`,
`number`, `item` FROM `Sheet2$` WHERE ((`number` = '{MERGEFIELD
number}'))"
\h }

This has really stumped me especially since I know that this is possible
any
help would be greatly appreceiated.

Thank you