Mail merge to Word 2007 - multiple data sources

Fev · External Since: Aug 06, 2009 Posts: 3

Hi
I am trying to set up a template for exam question papers for a
university. They would like to create a database of questions (with
allocated marks). As there are 4 types of questions a) Multiple
Choice b) Short questions c) Discussion questions and d) Essay
questions each with their own set of instructions, and slightly
different layout, I thought of doing the following:
1. Create a directory type mail merge document for each of the 4
question types.
2. Use the INCLUDETEXT field to bring these docments into the final
question paper
3. Use table calculations and bookmarks to calculate the marks per
section and total marks
A further requirement is to create a marking memorandum at the same
time. The marking memorandum would be generated in the same way,
using the same selection of questions, but merging the answer fields
into 4 seperate docs, and follow the same 3 steps as above.
Has anyone had experience in doing this who could point me in the
right direction. I would greatly appreciate any help/suggestions.
Regards
Heather

Graham Mayor · External Since: Jul 04, 2006 Posts: 4677

Although nothing to do with Mail Merge, something vaguely similar came up a
while back in a different forum. You *may* be able to adapt the suggested
method to your requirements or it may give you some ideas on how to progress
this issue.

http://groups.google.co.uk/group/microsoft.public.word.vba.general/bro..._thread

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Fev wrote:
> Hi
> I am trying to set up a template for exam question papers for a
> university. They would like to create a database of questions (with
> allocated marks). As there are 4 types of questions a) Multiple
> Choice b) Short questions c) Discussion questions and d) Essay
> questions each with their own set of instructions, and slightly
> different layout, I thought of doing the following:
> 1. Create a directory type mail merge document for each of the 4
> question types.
> 2. Use the INCLUDETEXT field to bring these docments into the final
> question paper
> 3. Use table calculations and bookmarks to calculate the marks per
> section and total marks
> A further requirement is to create a marking memorandum at the same
> time. The marking memorandum would be generated in the same way,
> using the same selection of questions, but merging the answer fields
> into 4 seperate docs, and follow the same 3 steps as above.
> Has anyone had experience in doing this who could point me in the
> right direction. I would greatly appreciate any help/suggestions.
> Regards
> Heather

Fev · External Since: Aug 06, 2009 Posts: 3

On Oct 18, 9:45 am, "Graham Mayor" <gma....RemoveThis@REMOVETHISmvps.org> wrote:
> Although nothing to do with Mail Merge, something vaguely similar came up a
> while back in a different forum. You *may* be able to adapt the suggested
> method to your requirements or it may give you some ideas on how to progress
> this issue.
>
> http://groups.google.co.uk/group/microsoft.public.word.vba.general/br...
>
> --
> <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
> Graham Mayor - Word MVP
>
> My web sitewww.gmayor.com
> Word MVP web sitehttp://word.mvps.org
> <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
>
>
>
> Fev wrote:
> > Hi
> > I am trying to set up a template for exam question papers for a
> > university. They would like to create a database of questions (with
> > allocated marks). As there are 4 types of questions a)Multiple
> > Choice b) Short questions c) Discussion questions and d) Essay
> > questions each with their own set of instructions, and slightly
> > different layout, I thought of doing the following:
> > 1. Create a directory type mail merge document for each of the 4
> > question types.
> > 2. Use the INCLUDETEXT field to bring these docments into the final
> > question paper
> > 3. Use table calculations and bookmarks to calculate the marks per
> > section and total marks
> > A further requirement is to create a marking memorandum at the same
> > time. The marking memorandum would be generated in the same way,
> > using the same selection of questions, but merging the answer fields
> > into 4 seperate docs, and follow the same 3 steps as above.
> > Has anyone had experience in doing this who could point me in the
> > right direction. I would greatly appreciate any help/suggestions.
> > Regards
> > Heather- Hide quoted text -
>
> - Show quoted text -

HI Graham
I have looked at this link, and was planning to have the entire system
under macro control, with a fill in form, for updating the initial
data such as faculty name and date of exam etc. The database of
questions as required in my brief is so that over a period of time a
large database of questions (per subject) can be created and when an
examiner wishes to set an exam they can choose a varied selection of
questions, Thus the files would not look exactly alike for each mail
merge session. For one exam they may have 10 multiple choice
questions, and 5 short questions and for another exam 8 multiple
choice and 6 short questions etc.

I have created a single Access database with a table for each of the 4
question types (as they require different fields) with relevant fields
and have got the question selection and mailmerge part to work. One
problem I have encountered, was using the AUTONUMLGL field to number
the questions works fine, but when I use the INCLUDETEXT to bring the
merge result files into the main exam template, I cannot restart the
numbering from 1 for each section. I have tried with both locked and
unlocked options. I may have to look at using the LISTNUM field with
the /s switch and using vba to renumber each question before updating
the INCLUDETEXT field using the /! switch.

I would greatly appreciate any further input, or suggestions of
another way to tackle this problem.
Regards
Heather

Graham Mayor · External Since: Jul 04, 2006 Posts: 4677

The only other method I can think of to deal with the numbering issue is to
insert a unique marker (here ##) rather than a number then replace the
markers with the numbers eg

Dim iNum As Long
iNum = 0
With Selection
.HomeKey wdStory
Do While .Find.Execute("##")
iNum = iNum + 1
Selection.Range.Text = iNum & ". "
Loop
End With

It is but a small extra step to break the document with section breaks and
number each section.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Fev wrote:
> On Oct 18, 9:45 am, "Graham Mayor" <gma....RemoveThis@REMOVETHISmvps.org> wrote:
>> Although nothing to do with Mail Merge, something vaguely similar
>> came up a while back in a different forum. You *may* be able to
>> adapt the suggested method to your requirements or it may give you
>> some ideas on how to progress this issue.
>>
>> http://groups.google.co.uk/group/microsoft.public.word.vba.general/br...
>>
>> --
>> <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
>> Graham Mayor - Word MVP
>>
>> My web sitewww.gmayor.com
>> Word MVP web sitehttp://word.mvps.org
>> <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
>>
>>
>>
>> Fev wrote:
>>> Hi
>>> I am trying to set up a template for exam question papers for a
>>> university. They would like to create a database of questions (with
>>> allocated marks). As there are 4 types of questions a)Multiple
>>> Choice b) Short questions c) Discussion questions and d) Essay
>>> questions each with their own set of instructions, and slightly
>>> different layout, I thought of doing the following:
>>> 1. Create a directory type mail merge document for each of the 4
>>> question types.
>>> 2. Use the INCLUDETEXT field to bring these docments into the final
>>> question paper
>>> 3. Use table calculations and bookmarks to calculate the marks per
>>> section and total marks
>>> A further requirement is to create a marking memorandum at the same
>>> time. The marking memorandum would be generated in the same way,
>>> using the same selection of questions, but merging the answer fields
>>> into 4 seperate docs, and follow the same 3 steps as above.
>>> Has anyone had experience in doing this who could point me in the
>>> right direction. I would greatly appreciate any help/suggestions.
>>> Regards
>>> Heather- Hide quoted text -
>>
>> - Show quoted text -
>
> HI Graham
> I have looked at this link, and was planning to have the entire system
> under macro control, with a fill in form, for updating the initial
> data such as faculty name and date of exam etc. The database of
> questions as required in my brief is so that over a period of time a
> large database of questions (per subject) can be created and when an
> examiner wishes to set an exam they can choose a varied selection of
> questions, Thus the files would not look exactly alike for each mail
> merge session. For one exam they may have 10 multiple choice
> questions, and 5 short questions and for another exam 8 multiple
> choice and 6 short questions etc.
>
> I have created a single Access database with a table for each of the 4
> question types (as they require different fields) with relevant fields
> and have got the question selection and mailmerge part to work. One
> problem I have encountered, was using the AUTONUMLGL field to number
> the questions works fine, but when I use the INCLUDETEXT to bring the
> merge result files into the main exam template, I cannot restart the
> numbering from 1 for each section. I have tried with both locked and
> unlocked options. I may have to look at using the LISTNUM field with
> the /s switch and using vba to renumber each question before updating
> the INCLUDETEXT field using the /! switch.
>
> I would greatly appreciate any further input, or suggestions of
> another way to tackle this problem.
> Regards
> Heather