Mail Merge Issue

[Dan S.]

I'm merging data from an Access database from a lookup cell. How can I
make the text from the lookup cell come up in the mail merge output?

TIA

 

[Tom Wickerath]

Hi Dan,

You work be much easier in the long run if you learn to avoid using table
lookups. Check out the 2nd Commandment of Access, here:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

I would create a query that includes the necessary tables to produce your
mail merge data, and then use this query as the source for the mail merge.
Also, you might want to take a look at what Access MVP Albert Kallal offers,
here:

Super Easy Word Merge (scroll down about half way on the page)
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Correction:

The first part should have read: "Your work will be much easier..."

 

[Ken Sheridan]

Dan:

Base the merge on a query which joins the table to the table from which the
'looked up' text is derived. You can then return the text field from that
table in the query rather than the foreign key field which references it.
Say for instance you have a table Contacts with a field CityID which
references the autonumber primary key CityID of a Cities table which contains
a text field City the you might use a query along these lines:

SELECT FirstName, LastName, Address1, Address2, City
FROM Contacts INNER JOIN Cities
ON Contacts.CityID = Cities.CityID;

In the above the FirstName, LastName, Address1 and Address2 fields are in
the Contacts table, the City field in the Cities table.

Ken Sheridan
Stafford, England

 

[Dan S.]

Ken Sheridan, if I was in the mood, I'd turn the lights down low and
reply with soft music, but you'll have to accept this instead::

Dan:

Base the merge on a query which joins the table to the table from which the
'looked up' text is derived. You can then return the text field from that
table in the query rather than the foreign key field which references it.
Say for instance you have a table Contacts with a field CityID which
references the autonumber primary key CityID of a Cities table which contains
a text field City the you might use a query along these lines:

SELECT FirstName, LastName, Address1, Address2, City
FROM Contacts INNER JOIN Cities
ON Contacts.CityID = Cities.CityID;

In the above the FirstName, LastName, Address1 and Address2 fields are in
the Contacts table, the City field in the Cities table.

Ken Sheridan
Stafford, England

I assume that would go either in the the criteria box on the Query
Design view?

 

[Dan S.]

Dan S., if I was in the mood, I'd turn the lights down low and reply
with soft music, but you'll have to accept this instead::

 

[Dan S.]

Dan S., if I was in the mood, I'd turn the lights down low and reply
with soft music, but you'll have to accept this instead::

Dan S., if I was in the mood, I'd turn the lights down low and reply with
soft music, but you'll have to accept this instead::

<corrected>

I think I found what you were doing in SQL view. I hope I can make it
work.

 

[Ken Sheridan]

That's right. The SQL statement is the query. You should be able to do it
pretty easily in query design view too; just add both tables, the one
containing the 'look-up' field and the one containing the text field. The
join is between the 'look-up' field and the primary key of the 'referenced'
table. Then add whatever fields you need from the 'referencing' table (the
one containing the 'look-up' field) and the text field from the 'referenced'
table. Or you can use the query wizard to build it.

Ken Sheridan
Stafford, England

: