Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Multiple merge data sources in 1 merge

    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

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple merge data sources in 1 merge

    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.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multiple merge data sources in 1 merge

    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.

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Multiple merge data sources in 1 merge

    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multiple merge data sources in 1 merge

    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

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple merge data sources in 1 merge

    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.

  7. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Multiple merge data sources in 1 merge

    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •