Compiling Survey Results 1

[ekim]

I have posted a similar question a couple of times in the past and did not get much response.

I have a series of surveys -- up to 50 items each in 5 surveys. The responses vary from yes/no to comments in memo fields -- i.e. "If Not, explain"--. The report needs to compile all the responses. I.e. on Item 1 35% said Yes, 60% No and 5% did not answer --, etc. -- along with all comments for certain prompts that should appear as a continuous group within the report in the same relative position to the simpler prompts that they had in the survey sheet. In other words using the above, say Item 2 was -- "If no explain", after showing the compiled response to Item 1 the report would display ALL the comments recorded to Item 2 before going on to Item 3 which may be another series of comments or could be a compiled Yes/No or other kind of quantified short answer response.

I can quantify the short answers. I can list out all the comments .. But I can't seem to do them both in the same report at the same level. If I do my calculations at a grouping level and the comments at the detail level (one subreport for each response) the order is incorrect.

I had considered either a separate query and separate subreport for each response (UGH!) or a set of intermediate tables to "pre-compile" my data before bringing them in the report (Double UGH).

Any thoughts. PLEASE

 

[Philly44]

I have some similar reports taht are run and although it took it a long time to set them up I have found that in a case like this a UNION query works best.

 

[ekim]

Thanks Philly. I'm not sure how to set up the union query in this case. Each survey has responses in its own separate table such that each respondent's return is a record and each response/answer in the particular survey is a separate field in the table. I'm not sure what I would be joining. Are you talking about a sort of self-join?

 

[Philly44]

I would assume that the tables have a one to many relationship based on a survey ID field. If thats the case then following might help.

Select AgeGroup as Entity, Count(*) as Frequency
From tblSurvey INNER JOIN tblSurveyInfo ON tblSurvey.SurveyID = tblSurveyInfo.SurveyID
WHERE AgeGroup Is Not Null
GROUPBY AgeGroup
HAVING AgeGroup Is Not Null
UNION
Select "First Visit" as Entity, Count(*) as Frequency
From tblSurvey INNER JOIN tblSurveyInfo ON tblSurvey.SurveyID = tblSurveyInfo.SurveyID
WHERE FirstVisit = YES;

What this will do is it will run the first select statement and the text value of the AgeGroup field in the variable Entity, and will also count all the instances of that field that aren't NULL. Then it groups them together. Then it runs the second the SELECT statement but this time because there are brackets around "First Visit" it will place that string into the Entity variable and will will return the total amount of Yes's in frequency.
To make this usefull setup a a report with this query as a the control source. Then place 2 text boxes in the detail section and set their control source to Entity and Count respectively and when you run the report it will keep re-generating these boxes for every UNION you have.

I hope this helps

 

[ekim]

Sounds very intriguing Phiily. Thank You. I'll give it a try.

Just to clarify my setup -- perhaps survey was a bit misleading. These are actually more like evaluation questionnaires.

Each table is basically a stand-alone data repository so there are no relationships. Each record IS stamped with the survey ID so I can do the inner join you suggest.

My main problem was trying to have calculated results from a compillation of a given field ( say, how many said yes or no on question 1) appear in the same report and in the original sequence as basicaly a data dump of all responses to another field (a memo field -- if no, explain) followed by the calculations on the next field, and so on.

What it seems you are trying to get me to see is that I can use the inner join as a sort of roll-up for the calculations and then list the memo as details.

Thanks again