VBA to loop through fields in records in a report

[jpmoriarty]

I have a database that's probably poorly designed, but it exists and I need to use it!

It's a database that collects responses to a questionnaire, in effect. Each row represents a response, and each row contains personal data (name, email etc) and then 69 fields that are the responses to the questions (Q1-Q69). There are then 69 separate fields which are basically an "agree/disagree" for each of the 69 questions (Q1ad - Q69ad)

I've done the easy bit - a report that looks up Q1 and Q1ad and displays the report for all 700 or so responses. The report also groups the responses by respondent type (e.g. individual, organisation, company - of which there's a table in the database containing the 15 types). But what I need is for the report to do this for a selection of the questions - e.g. 1-40, 41-50 and 51-69. Clearly I don't want to have to drag and drop each of the fieldsets as that'll take forever! I don't mind if the VBA code creates 69 individual reports and then a separate bit of VBA that combines the reports into one (or three, as above) big report(s) at the end or if it just whirs away and creates one report - but the end result needs to be:
Q1
--Respondent group 1
----A1 (answer and agree/disagree)
----A1 (answer and agree/disagree)
--Respondent group 2
----A1 (answer and agree/disagree)
--Respondent group 3
----A1 (answer and agree/disagree) [... until all 700 responses done]
Q2
--Respondent group 1
----A2 (answer and agree/disagree)
----A2 (answer and agree/disagree)
--Respondent group 2
----A2 (answer and agree/disagree)
----A2 (answer and agree/disagree)
----A2 (answer and agree/disagree)
--Respondent group 3
----A2 (answer and agree/disagree) [... until all 700 responses done]
[... until up to whatever upper question limit - e.g. 40 as in example above]

Any suggestions as to how I can get around this? I don't want to have to create 69 reports, all with different queries but the same layout. And pulling them together into these monster reports of multiple questions is really key.

Very grateful for any assistance you can provide, and my thanks in advance.

 

[wiklendt]

instead of looking up Q1 and printing it, and making that happen for all 69 questions, why not put ALL the questions in the report, and get access to GROUP BY question?

the way you do this would depend on what version of access you are using.

in 2007 it's very very easy:

in design view of the report, get to the "design" ribbon and click on "group and sort" button. click on it.

another pane will appear at the bottom of the screen where you can "add a group" or "add a sort".

click on "add a group", then in the dropdown that it gives you, select "YourQuestionNumberField". access will change the design of your report automatically to create the appropriate grouping.

now when you view your report, all the respondent details and their answers will appear grouped by question number, just like you requested in your post.

 

[jpmoriarty]

Would that mean I have to degisn a form with all 69 questions in it, and all 69ad fields as well? I'm not sure I understand whether access can group in that way - let's say there was only one record in the database, I'd need access to group by each question in that one record. As each question is a separate field within the same record, can it do that? I think it could if the DB had been designed so each user was on record in a user table and each question a separate question in another table, but I don't see that this can work when each question is a field within one record.

e.g. table field structure

id | name | email | DateCompleted | Q1 | Q2 | Q3 | ... | Q69 | Q1ad | Q2ad | Q3ad | ... | Q69ad

 

[wiklendt]

As each question is a separate field within the same record, can it do that? ... I don't see that this can work when each question is a field within one record.

e.g. table field structure

id | name | email | DateCompleted | Q1 | Q2 | Q3 | ... | Q69 | Q1ad | Q2ad | Q3ad | ... | Q69ad

oh. uhm, no, you can't do anything easily at all with that kind of table structure.

i see your dilemma. are you prepared to normalise? or are you on a restricted time-frame?

 

[wiklendt]

as an ugly hack, you could copy and paste your table into an excel document and manipulate there (depending on how familiar you are with access/excel) - only thing is it won't update as your data does, so this method is only really good for completed data sets (unless you code an export or something)