Hi guys!

Basically, my problem (which I'm sure is a common one), is that I'm
trying to merge, from an access database query, fields to a single
document, but they come out on multiple documents.

I am basically trying to create a kind of examination report card for
students. There is a candidate form for their name, dob etc, and a
subform for their exam subjects and corresonponding grades. I want all
these grades and subjects to appear on each candidate's card, not a new
card for each candidate's subject and grade

Basically, this is what I want using fields name, dob, and subject and
grade (last two in subform)

Bobby Smith dob 19th July 1990
English Grade B
Spanish Grade D

but at the moment I'm getting:

Bobby Smith dob 19th July 1990
English Grade B

Bobby Smith dob 19th July 1990
Spanish Grade D

two documents instead of one.

Now I have had a look for possible solutions to this, and have found a
microsoft site

http://support.microsoft.com/?kbid=105888

which I do believe gives a solution to the problem, but I don't
understand it enough to change it to my context. This was found via
http://homepage.swissonline.ch/cindymeister

so if you could help me out by spelling it out a bit, that would be
great, as this would be such a great help!

Thank you very much in advance!

Contro.

Use a report in Access rather than mailmerge to word. With the grouping
capability that is available to you in an Access report, it is by far the
easiest way to do this.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Contro" wrote in message
ups.com...
Hi guys!

Basically, my problem (which I'm sure is a common one), is that I'm
trying to merge, from an access database query, fields to a single
document, but they come out on multiple documents.

I am basically trying to create a kind of examination report card for
students. There is a candidate form for their name, dob etc, and a
subform for their exam subjects and corresonponding grades. I want all
these grades and subjects to appear on each candidate's card, not a new
card for each candidate's subject and grade

Basically, this is what I want using fields name, dob, and subject and
grade (last two in subform)

Bobby Smith dob 19th July 1990
English Grade B
Spanish Grade D

but at the moment I'm getting:

Bobby Smith dob 19th July 1990
English Grade B

Bobby Smith dob 19th July 1990
Spanish Grade D

two documents instead of one.

Now I have had a look for possible solutions to this, and have found a
microsoft site

http://support.microsoft.com/?kbid=105888

which I do believe gives a solution to the problem, but I don't
understand it enough to change it to my context. This was found via
http://homepage.swissonline.ch/cindymeister

so if you could help me out by spelling it out a bit, that would be
great, as this would be such a great help!

Thank you very much in advance!

Contro.

Thanks for your reply!

If only I could use a report; the document is in a complex template
which I do not think can be replicated in an Access Report.

I'm sure there is a way to get this working okay. I will need it for
another grade card, but only on another template.

Please keep the help coming!

Thank you once more.

Contro.

Without seeing your template, I can't be sure, but I have created some very
complex reports in Access. In most of those cases, there was absolutely no
way that it could have been done using mailmerge.

Another alternative would be to use a couple of mailmerges, a formletter
merge and a catalog (or directory) merge and then use a macro to combine the
relevant parts of the catalog or directory merge with the documents created
by formletter merge.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Contro" wrote in message
ups.com...
Thanks for your reply!

If only I could use a report; the document is in a complex template
which I do not think can be replicated in an Access Report.

I'm sure there is a way to get this working okay. I will need it for
another grade card, but only on another template.

Please keep the help coming!

Thank you once more.

Contro.

Hi Contro,

http://support.microsoft.com/?kbid=105888

which I do believe gives a solution to the problem, but I don't
understand it enough to change it to my context.

If you aren't comfortable with Doug's suggestions, tell us where
you're getting stuck with the technique described in the KB
article.

To describe in laymen's terms the underlying principle:

Each "main entry" (the student) must be uniquely identifiable
(usually something like an ID field). All the "sub entries" that
belong to the main entries must also contain this field.

The data must be sorted on this field, so that all the entries
for an individual are grouped together. this means the mail merge
will process the records in exactly this order.

The complex field set the KB article postulates picks up this ID
field when record is merged. It assigns this value to a "holding
tank" at the end of the record merge. At the beginning of the
record merge, these two values are compared. If they match, then
the "list" part of the IF field is executed. If they do not
match, then the "main entry" part of the IF field is executed.

The tricky part when setting this up is usually understanding how
it's supposed to work, and then getting the "main" and "list"
parts into the IF field correctly.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8
2004)
http://www.word.mvps.org

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

The main problem is that the template has a very specific layout which
really isn't open to any change what so ever, not even minor changes,
and that to replicate it would be very hard, and I'm not sure totally
possible.

The other main problem is that producing it as a report would bring
it's own problems: there are other fields in the document, such as the
address details of who the results are being sent to, and the mail
merge picks up which fields are blank, and then misses them out;
doesn't print commas after these blank fields too; and also doesn't
print the country if the country is the the UK. This might well be
possible to do in a report, but I don't know how, and would then have
to research this too.

I didn't know you could combine two merges, or what not, using a macro
though. I shall have a look into this after trying out Cindy's
solution, as I am quite sure that does provide a solution, from the
example I posted; I just need to understand it.

Thanks for your help! I'll let you know on Tuesday how this goes, as I
won't be able to give it a try before then.

Contro.

Doug Robbins - Word MVP wrote:
Without seeing your template, I can't be sure, but I have created some very
complex reports in Access. In most of those cases, there was absolutely no
way that it could have been done using mailmerge.

Another alternative would be to use a couple of mailmerges, a formletter
merge and a catalog (or directory) merge and then use a macro to combine the
relevant parts of the catalog or directory merge with the documents created
by formletter merge.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Contro" wrote in message
ups.com...
Thanks for your reply!

If only I could use a report; the document is in a complex template
which I do not think can be replicated in an Access Report.

I'm sure there is a way to get this working okay. I will need it for
another grade card, but only on another template.

Please keep the help coming!

Thank you once more.

Contro.

Hi Cindy,

Thanks for you reply. My database is fully normalised (so far as is
required anyway! I'm no expert on it, but it's all up so the
relationships are right and the primary and foreign keys are in place).

The code of the knowledge base example is:

Employee Project
{MERGEFIELD Employee} {MERGEFIELD Project}{set duplicate {if
{MERGEFIELD Check}="1" "off" "on"}}{nextif {mergefield check}=""}{if
{duplicate}="on" "P
t{MERGEFIELD Project}"}{set duplicate {if {MERGEFIELD Check}="1"
"off" "on"}}{nextif {mergefield check}=""}{if {duplicate}="on" "P
t{MERGEFIELD Project}"}{set duplicate {if {MERGEFIELD Check}="1"
"off" "on"}}{nextif {mergefield check}=""}{if {duplicate}="on" "P
t{MERGEFIELD Project}"}{set duplicate {if {MERGEFIELD Check}="1"
"off" "on"}}{nextif {mergefield check}=""}{if {duplicate}="on" "P
t{MERGEFIELD Project}"}

From what you mention, I see that the code basically looks for items
which have a primary key - foreign key match (subjects that belong to
the same student). I'm just not sure how to use this code to my
situation though. Above, the code seems to be looking for a number in
a "check" table which is 1; I've no idea why. Also, I do not
understand all the "off" "on" business at all or how they relate to the
"1" that is specified and whether they need to relate to a 1, or if
this is just something that is only part of this example and isn't
necessary (not something I'd need to use in my own database).

Basically, I'm lost! Please help! :-)

Contro.

Hi Contro,

OK, I read your message to Doug and you mention "very exact formatting".
Before we get too deeply into this approach, could you describe what kind
of formatting is meant?

IF fields do a fairly good job with Font formatting. Paragraph formatting
can get a bit "iffy" under some circumstances. If things are really
complex, a macro solution might be the better approach.

As to the article and the example:

I see this is an older article. A better example is here
http://support.microsoft.com/?kbid=211303 (thanks for asking and thus
bringing this to my attention). This article should make more sense (and
I apologize for the confusion due to the old article!)

Thanks for you reply. My database is fully normalised (so far as is
required anyway! I'm no expert on it, but it's all up so the
relationships are right and the primary and foreign keys are in place).

The code of the knowledge base example is:

Employee Project
{MERGEFIELD Employee} {MERGEFIELD Project}{set duplicate {if
{MERGEFIELD Check}="1" "off" "on"}}{nextif {mergefield check}=""}{if
{duplicate}="on" "P
t{MERGEFIELD Project}"}{set duplicate {if {MERGEFIELD Check}="1"
"off" "on"}}{nextif {mergefield check}=""}{if {duplicate}="on" "P
t{MERGEFIELD Project}"}{set duplicate {if {MERGEFIELD Check}="1"
"off" "on"}}{nextif {mergefield check}=""}{if {duplicate}="on" "P
t{MERGEFIELD Project}"}{set duplicate {if {MERGEFIELD Check}="1"
"off" "on"}}{nextif {mergefield check}=""}{if {duplicate}="on" "P
t{MERGEFIELD Project}"}

From what you mention, I see that the code basically looks for items
which have a primary key - foreign key match (subjects that belong to
the same student). I'm just not sure how to use this code to my
situation though. Above, the code seems to be looking for a number in
a "check" table which is 1; I've no idea why. Also, I do not
understand all the "off" "on" business at all or how they relate to the
"1" that is specified and whether they need to relate to a 1, or if
this is just something that is only part of this example and isn't
necessary (not something I'd need to use in my own database).


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

Hi Cindy,

Thank you for another prompt reply.

Oh yes, don't worry, the formatting issue is not to do with how the
merges themselves appear, but the layout of the report itself. It is
very complicated, sporting different font styles, borders, backgrounds
etc all over the place; it also needs to be easily changed by other
members of staff who can use Microsoft Word, but not microsoft Access,
nevermind the report editor.

So basically, I'm still just stuck on the things mentioned in my
previous message to you (please see below), and am just not sure what
each part of the code does in that example in order to change it to fit
my needs.

If you could explain the following sample of code, that would be great!

{MERGEFIELD Employee} {MERGEFIELD Project}{set duplicate {if
{MERGEFIELD Check}="1" "off" "on"}}{nextif {mergefield check}=""}{if
{duplicate}="on" "P t

I realise P and T mean Paragraph and Tab, so understand those; it's the
point of the "1", "off" and "on" that I am not sure of, and just what
the code is doing in relation to those. I'm certain that this code is
doing just what I require, but just do not know how to alter it for my
scenario.

As always, your help is very much appreciated!

Contro.

Cindy M -WordMVP- wrote:
Hi Contro,

OK, I read your message to Doug and you mention "very exact formatting".
Before we get too deeply into this approach, could you describe what kind
of formatting is meant?

IF fields do a fairly good job with Font formatting. Paragraph formatting
can get a bit "iffy" under some circumstances. If things are really
complex, a macro solution might be the better approach.

As to the article and the example:

I see this is an older article. A better example is here
http://support.microsoft.com/?kbid=211303 (thanks for asking and thus
bringing this to my attention). This article should make more sense (and
I apologize for the confusion due to the old article!)

Thanks for you reply. My database is fully normalised (so far as is
required anyway! I'm no expert on it, but it's all up so the
relationships are right and the primary and foreign keys are in place).

The code of the knowledge base example is:

Employee Project
{MERGEFIELD Employee} {MERGEFIELD Project}{set duplicate {if
{MERGEFIELD Check}="1" "off" "on"}}{nextif {mergefield check}=""}{if
{duplicate}="on" "P
t{MERGEFIELD Project}"}{set duplicate {if {MERGEFIELD Check}="1"
"off" "on"}}{nextif {mergefield check}=""}{if {duplicate}="on" "P
t{MERGEFIELD Project}"}{set duplicate {if {MERGEFIELD Check}="1"
"off" "on"}}{nextif {mergefield check}=""}{if {duplicate}="on" "P
t{MERGEFIELD Project}"}{set duplicate {if {MERGEFIELD Check}="1"
"off" "on"}}{nextif {mergefield check}=""}{if {duplicate}="on" "P
t{MERGEFIELD Project}"}

From what you mention, I see that the code basically looks for items
which have a primary key - foreign key match (subjects that belong to
the same student). I'm just not sure how to use this code to my
situation though. Above, the code seems to be looking for a number in
a "check" table which is 1; I've no idea why. Also, I do not
understand all the "off" "on" business at all or how they relate to the
"1" that is specified and whether they need to relate to a 1, or if
this is just something that is only part of this example and isn't
necessary (not something I'd need to use in my own database).


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

Hi again, Cindy,

I'm very sorry, I think I didn't look at your example well enough, and
only saw the bottom part which said "How to format key fields"; I
wrongly assumed you had misunderstood my problem, and thought it was
the format of the mail merge that was causing my woes.

I've looked at the example, and indeed your message, again and realised
I read it all wrong! I'm sorry for that; I guess I was not as alert
since it was a Saturday (I'm sticking to that excuse!).

Thank you very much, I'll try out that example on Tuesday when I'm back
with the database, and will let you know the results! I don't know why
some of the old code isn't there anymore, but I'm guessing the "1"
mentioned is just a way of expressing that the keys match. At any
rate, if it works, that is all that matters!

Thank you once again. I'll be sure to let you know!

Hope you, and Doug, have had, or are having, a good weekend :-)

Contro.

Hi Contro,

I've looked at the example, and indeed your message, again and realised
I read it all wrong! I'm sorry for that; I guess I was not as alert
since it was a Saturday (I'm sticking to that excuse!).

Thank you very much, I'll try out that example on Tuesday when I'm back
with the database, and will let you know the results! I don't know why
some of the old code isn't there anymore, but I'm guessing the "1"
mentioned is just a way of expressing that the keys match. At any
rate, if it works, that is all that matters!

I'm sorry I didn't formulate my response any more clearly; my head was
trying to deal with correcting the link on my website and answer you...

When I looked at the example you showed me and saw the "NextIF" field,
the alarm bells went off. SkipIf and NextIf are "deprecated" (to use MS
parlance for things they don't support any more). Once you dig into the
newer article I think you'll find it makes more sense when thinking in
terms of a key field.

The key value of the current record will be compared against the value of
the last record (which is why the records have to be sorted according to
key field in the data source). If the two match, the part of the IF will
be executed that creates the list. If they differ, then it put out the
text for the "one" side of the equation.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

Hi Cindy,

:-) There was nothing wrong with your response, don't worry; it was
very helpful and informative, as always. I've tried what you said, but
am still having trouble though (typically!).

Firstly, well, it was difficult, as there are many merge sections on
the word template, but basically the code works, but doesn't do what I
want it to do (at least I think it's working right!). What happens is
that it does remove all the other merges other than the result and
grade, but it still keeps them on separate pages (so I just have a
blank page with the subect and grade on, with another page with all the
details as well as one of the subjects).

e.g.

Bob Smith
Geography Grade C

page break

Mathematics Grade C (no other details).

So basically I just want to remove this page break (a page for each
person and their results) I'm not sure if this should be happening
anyway really, as surely the code would remove any page breaks in order
for it to have any point!

It's so annoying :-( You'd think it would be a straight-forward thign,
as surely this would be a common thing to desire in a merge!

I hope you can help! Thank you for your patience, Cindy.

Contro.

Cindy M -WordMVP- wrote:

Hi Contro,


I'm sorry I didn't formulate my response any more clearly; my head was
trying to deal with correcting the link on my website and answer you...

When I looked at the example you showed me and saw the "NextIF" field,
the alarm bells went off. SkipIf and NextIf are "deprecated" (to use MS
parlance for things they don't support any more). Once you dig into the
newer article I think you'll find it makes more sense when thinking in
terms of a key field.

The key value of the current record will be compared against the value of
the last record (which is why the records have to be sorted according to
key field in the data source). If the two match, the part of the IF will
be executed that creates the list. If they differ, then it put out the
text for the "one" side of the equation.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

Hi Contro,

What happens is
that it does remove all the other merges other than the result and
grade, but it still keeps them on separate pages (so I just have a
blank page with the subect and grade on, with another page with all the
details as well as one of the subjects).

Try setting the mail merge type to Catalog (or Directory if this is
2002/2003). That should remove any "next page" stuff that you haven't
manually entered into the main merge document.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

Hi Cindy,

Thanks for your help. I have tried what you said, but still get the
same problem. I'm going to try breaking down my merge to make sure it
would work on a simple example, and that it's not something I'm doing
wrong later on (something I should have done earlier perhaps!).

I'll let you know how it goes. Hopefully I'll get there!

Thanks again!

Contro.


Try setting the mail merge type to Catalog (or Directory if this is
2002/2003). That should remove any "next page" stuff that you haven't
manually entered into the main merge document.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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