Moving to <<First Record>> when merging with Excel document

[Kelly Schumacher]

Word 2002. I am merging a Word document with an Excel
Spreadsheet. I notice that there is a <<Next Record>> in
the "Insert Word Field" drop down box. I am looking for
a way to go back to the first record in the Excel file
part of the way through the Word file. Can anyone help?

Thanks,

Kelly

 

[Peter Jamieson]

Word doesn't have a <<First record>> type field. Although there are ways
(for example) to duplicate the data in your Excel file so you get records 1
to n, then start with 1 again, your requirement sounds a bit more
complicated - maybe you could provide more detail on what you need to do?

 

[Kelly Schumacher]

We have a variable N, so repeating the records would work
for only one use of the file. We need to use the file as a
template, allowing for N to be any number (typically
ranging from 10 - 200).

We are working with survey data. For each question we are
showing the frequency distribution of responses, the mean,
and the standard deviation. Each value is in a cell across
the top of the spreadsheet. For example, the frequency of
respondents selecting A for question 1 is in cell A1, the
frequency of respondents selecting B for question 1 is in
cell A2, etc.

This part works fine because there is only one value
reported for each result.

The problem we're having is trying to display the comments
that go with each question. Each quantitative question
(likert scale) is followed by an open ended question. We
want the Word document to show the summative data for the
quantitative question and then list the responses to the
open ended questions.

We are able to do this through only the first question.
The quantitative data appears at the top, the open ended
responses are listed below using:

<Q1-example><Next Record>
<Q1-example><Next Record>

This list continues for up to 200 rows, allowing between 1
and 200 reponses.

Then, we insert the fields for question 2, the same as
question 1, and nothing comes up for the Q2 examples. The
reason for this is that Word is looking down the Excel
file at a row that is below the end of the data. We need
Word to return to the first record.

Does this make sense?

Thanks,

Kelly

 

[Peter Jamieson]

First, it sounds to me as if you might be better off storing your data in a
database package such as Access and producing your reports from there. e.g.
it sounds as if your data would be better structured as two tables:

Question_stats with fields
Question_ID
Response_a_count
Response_b_count
etc.

Response_texts with fields
Question_ID
Response_sequence_number
Response_text

If Word is the preferred option, bear in mind that it isn't designed for the
kind of thing you're doing. You'll find some information on ways to deal
with this general kind of thing at fellow MVP Cindy Meister's web site at

http://homepage.swissonline.ch/cindymeister/

(Look for Mail Merge FAQ, Special merges, Multiple items per condition). My
guess is that using the DATABASE field approach may be what you need, but it
depends on how your data is structured in Excel - I would certainly need to
know exactly how the text responses are stored, for example.

Another approach would be to avoid mailmerge altogether and write Word VBA
to get the data from the Excel sheet and insert it into a Word document. You
can get the data using ADO or via Automation. This approach would give you
the most control over layout etc.