Multiple merge data sources in 1 merge

Topic

New Reply

Office 97 – I’m helping a consultant who is plans to store answers to about 1000 questions in Access. Because of Access’ table limitations that means 4 tables per “consult”. For the best formatting capabilities, we plan to merge to Word for the report. How can I have one long report document use 4 separate data sources? Or should I be doing things very differently?
Thanks
Nora

Reply | Quote

Replies

Nora,

Can’t you create a datasource which has a query as its source- and that query joins the 4 tables?

Otherwise, the “not nice” answer would be to use VBA.

Reply | Quote

I’d love to find a non-VBA way to deal with this. I can’t do it with a query, because queries also have size limitations – probably the same number of fields as tables have (255). My data source is going to need all 999 or so fields!

I tried breaking the word document into sections and using different data sources for each section. That seemed like such a good idea that didn’t work.

Reply | Quote

I think your data structure is not optimised in Access. You should have two tables linked in a 1 to many relationship and be using forms and sub-forms (or reports and sub-reports).

Table 1 fields along the lines of – ID_1(as the PK), Name, Address etc
Table 2 fields along the lines of – ID_2(as the PK), ID_1 (allow duplicates and linked to Table 1), QuestionNumber, QuestionResponse

Table 1 would then contain records of say 100 people who did the questionnaire and Table 2 would have records of 100 x 1000 in which each thousand questions had the same ID_1 field value

You may have to do your reports from Access. If you want to try further with your current setup the following may assist as workarounds.

Concepts for testing on doing a mail-merge over 1000 fields are:
1. It may be possible to interleave the query so that every
four rows make up the set of data such that
Row n = Q1-250
Row n+1 = Q251-500
Row n+2 = Q501-750
Row n+3 = Q750-1000
Then use the next record field to step through all four
rows before repeating the merge steps on to the next
2. Do each table of questions as separate mail merge files.
Each merge would probably need to restart the page numbering
3. Another database without the restrictions that Access has.
What are the field count restrictions for MySQL, SQLServer,
Oracle, SAP etc.

Reply | Quote

The original request was for some way to go to a business, fill out 1000 questions – he is analyzing the business and he obviously has a lot of things to ask. Then he wants to have the report filled out automatically from his data input. He won’t have hundreds of records – just a few records with lots of fields. Thus no need for one-to-many links.

He was contemplating building the whole thing in Word, but he wanted choice lists and option lists (Yes/No/NA) for ease of data entry. I thought Access would prove easier to build the data entry part and that’s the way we started.

I need to consider your merge comments, but I think you are suggesting the report be broken into multiple documents – and that may work just fine. I wanted to know if I were overlooking something obvious or whether a work-around is available. You are telling me that there is no simple way to do what we need to do, and for that I thank you.
Nora

Reply | Quote

Hi Sanora:

I am definitely not an Access expert. But I can’t see that answers to a thousand questions requires a thousand fields. I think that Andrew is right. In fact, if all he wants is to put answers to questions in a report, Word can handle that. After all, if you’re about to put a thousand field names into a table, you can put a thousand bookmarks for each answer & use INCLUDETEXT fields to reference them. If I were you, I’d look for an easier way.

If you have more specifics, perhaps someone here could come up with an idea.

Reply | Quote

Well that makes it a whole lot easier.
What about using a Word form. You can set it up with all the questions and then specify each of the field types to provide default text, drop downs, tick boxes etc. Once the form is protected you are in data collection mode and the completed document is ready to print instantly without any merge.
If you like, you can then save the responses as a data file for input into a database.

Reply | Quote