73 Question Assessment vs. 754 Control Limit 1

[LowBrow]

I have been beating my head against this for the last 3 days.
I have a db that has as a component a 73 question assessment. The assessment has yes/no, memo, combo boxes, and multiple choice questions (set as yes/no controls). Some of the multiple choice questions offer up to 15 options.
The table structure has a master table (PCPABCID) with the selected client, assigned staff, and date of the assessment. Each question then has a table for respective answers, tied to the master record by the PCPABCEID foreign key.
I have built sub reports for each answer table, but access will not allow me to build the complete report as it says I have hit the maximum control limit for the report. I have not done a complete tally, but it seems reasonable that I would. I did not run into this problem when designing the forms as I use dynamic switching to flow through the sub forms.
Aside from redesigning the entire assessment (which is not within my power to do), I am looking for options to work-around this issue.
So here are my questions:
Is it possible to programmatically 'append' reports with respect to page numbering and printing, so that when the user opens the master report, associated other reports would be opened and appended to document in a particular order when viewed or printed?
Is it likely that I have a separate issue here in designing the report, or associated query/table structure(s) that is adding to this problem?
Also, if you have any recommendations from previous experience that may solve this delimma, I would appreciate hearing that, too.

 

[LowBrow]

I have answered this on my own. I am not sure why this worked, but it worked. Previously, when adding the sub reports to the main report, I used the wizard. This would trigger the 'too many controls' error at around question 15, which was far less than the allowable number of controls, but I noticed at this point, too, that the design view detail grid would not let me extend any further.

I started with a fresh 'Main Report'. In the design view on the details grid, I added 73 unbound sub reports, and put them at .25" height. I then went back through and manually associated each of these with the respective sub report, saving after associating each. After every 10th question, I closed out and recompacted the db. The report loads quickly and runs fine.

 

[dhookom]

I appreciate your delima "which is not within my power to do" but I would do everything I could to normalize the tables to make reporting simple. I would attempt to take the "spreadsheet" table and use code to push the data into a more normalized structure, even if just temporary.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LowBrow]

Thanks Duane! I did actually attempt that, though it may not have been in the best manner. What I did was make outer joins to each of the individual question tables, tied by the PCPABCID. When I got to around question 30, it said the query was too complex. In retrospect, I probably should have made a table, and applied idividual queries append data to it. Would I run into a similar problem with such a table, or are recommending winnowing the questions themselves to single field answers? One thought along those lines was to code numeric, comma-delimited answers for the multiple choice questions to a single field. So that each option would be represented by a number, and as each was selected or deselected, the field would update appropriately. Are these similar to what you were recommending, or is there something else I have missed here? Any advice will help me in the future!

 

[dhookom]

Take a look at my "At Your Survey" application/demo at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

You might need some additional functionality but this should give you an idea of how to create a normalized survey application.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LowBrow]

Thanks! I will check it out!