Access to Word MailMerge Question

[Marc R Fienman]

Hello everyone,

Here's the issue. I have a query with a one to many
relationship. Let's say there are three fields, Number,
Picture, Text.....Now for instance I would want to print
Number 1, Picture 1, Text 1, Text 2, Text 3, then Picture
2, Text 1, Text 2, Text 3, then start a new page and then
Number 2 ad nauseum. Now the way the mailmerge feature
works, it creates a new page for each record in the
query, but since I have a one to many relationship, it
doesn't print the way I want it to. Instead of the
above, it prints, Number 1, Picture 1, Text 1, NewPage,
Number 1, Picture 1, Text 2, etcc....The solution I came
up with is to loop through each of the three tables and
print the text directly from Access using VBA code, but
this makes it difficult to change formatting except using
code. Does anyone have any suggestions regarding doing
this using MailMerge so that I can format fields in a
template? Is this even possible? For the life of me I
can't figure out any way to do this.

Thanks,
Marc R. Fienman

 

[Marc R Fienman]

Cindy,

Yeah....I did look at that, but I am a little miffed by
the Microsoft knowledge base article on using "set"
and "IF" statements in the merge field....Not to mention
that they use a "1" to determine the last instance in the
query of the name.. I have a query with 100 records.
How woul I create a calculated field to put a "1" in the
record of the last instance of a particular name?

Also, doing it the "merge" way, is there anyway to insert
a picture object that is NOT embedded in the database
during this type of merge? There is only the path name
in the database.

I basically did it the way of just using VBA code to
write the document, but my client wants more control over
formatting a template which she can do with a merge field.

Thanks,
Marc

 

[Cindy Meister -WordMVP-]

Hi Marc,

I am a little miffed by
the Microsoft knowledge base article on using "set"
and "IF" statements in the merge field....Not to mention
that they use a "1" to determine the last instance in the
query of the name.. I have a query with 100 records.
How woul I create a calculated field to put a "1" in the
record of the last instance of a particular name?

I think you misunderstood that, a little. Granted, it's
complicated to get set up. But you certainly don't need to
use "1" to determine the last instance of anything. What you
DO need is an "identifier field" in the table with the many
data that always repeats until the next "main record"
changes. This does not need to be displayed in the merge
result. After all, 1:many in a database requires the same
thing - a key field that links the two tables together.

Also, doing it the "merge" way, is there anyway to insert
a picture object that is NOT embedded in the database
during this type of merge? There is only the path name
in the database.

Actually, a link is the ONLY way to insert a graphic object
as part of the merge process. In that same section of my
mail merge FAQ you'll find the instructions for merging a
picture from a data source - and this has to be done with a
file path, as part of an IncludePicture field.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan
24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail