Gigantic merge (97)

[sanora]

The results of a survey make up a recordset in Access. There are nearly 400 questions, so for each person surveyed, there will be 400 records, each the answer to a question. I need to create a print-out of the answers from one person. Because of the formatting required, I planned to merge to Word.

Typically a merge uses multiple fields of one record in Access. I want to use a single field from multiple records. Can I do that? I think I'm going to run into problems!!

[gwhitfield]

Nora,

When you are talking about merging to Word, are you talking about Mailmerge?

I hadn't done this before, but I was able to insert an SQL query into mailmerge using MSquery (Using tools, mailmerge, get data, open data source, and digging around).

So if you were able to get all responses into a single SQL query, you would be able to use this as a datasource to create your Word document.

[sanora]

I can't put that many fields in a query. I think the maximum is 200-ish.

[gwhitfield]

OK, I understand now.

You will probably have to resort to Word VBA. You can run queries from Word which enquire into an Access database- you would be able to create a series of queries- and then populate the document. You could combine it a mailmerge. The first 200 fields populated by mailmerge- but the mailmerge is driven by VBA code, and not the default interface. You can move to the next mailmerge record with<pre> ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord</pre>


Create bookmarks for the other fields- and then interrogate the Access database, to populate bookmarks:<pre>ActiveDocument.Bookmarks("q202").Ra nge.Text = rs.fields("Q202")</pre>


Just some thoughts to get you started