Mail Merge Problem

[rm51]

I have an Access 2003 database that I am merging into Word 2003 documents.
One of the Access fields is a number field that uses another table to lookup
the values. When the field is merged, I am getting the Primary Key field
from the lookup table instead of the column I have asked for. Any idea why
the merge is grabbing the wrong column of my lookup table?

 

[Peter Jamieson]

It's a while since I have heard this one, but I think the problem is that
the primary key field in the lookup table /is/ the "foreign" key in the main
table whose data you are retrieving. So although the Access user interface
automatically substitutes a value from the lookup table, the OLE DB provider
that Word uses to get the data does not - it just returns the value in the
table you specified, which is actually the primary key value.

I think you will have to write a query in Access that explicitly joins the
two tables, and use that as the data source, to solve this problem - e.g. if
the main table is tablem and the foreign key column name is xm, the lookup
is tablep and the primary key field is xp and the value you actually want is
yp, then something like the following may do the trick:

SELECT m.*, p.yl
FROM tablem m LEFT JOIN tablep p ON m.xm = p.xp

I don't know if it will work but it is probably worth trying.