Statistical analysis of results of questionnaires

[ouioui]

I use FMpro10 Advanced on Windows XP Prof 2002. I'm a "Junior" programmer - so far, have mostly designed donor management d-bases for community groups (relationships - one to many). Not as familiar with many to many relationships, but learning with the help of FM Training Series.

My challenge: to be able to do statistical analysis, between any two dates (i.e. monthly or yearly reports) of the results of 3 separate questionnaires. The problem is, some of the questions have multivalued attributes, and the user can select more than one answer. I need to analyse the results for each of the values selected for that question. I therefore cannot use straightfoward subsummary reports.

There are 3 questionnaires (Q1 = daily calls; Q2 = client meetings; Q3 = other activities.) There's some overlapping of questions and possible answers between the three.

Stats should include - simple analysis: totals, averages, standard deviation; as well as some cross analysis (for Q2) between three populations which are identified through one of the questions.

Here's my limited analysis of what may be needed in terms of tables and relationships:

table 1: questionnaire (A questionnaire can have several questions; a question can belong to more than 1 questionnaire)

table 2: questions (A question can elicit more than one response; a response can belong to just one question, I think)

table 3: responses (A response can have multivalues and can generate statistics; the stats are related to just one response; again, not sure my reasoning is accurate here)

table 4: stats

Summary of my Questions:

1) What would the entity-relationship diagram (ERD) look like? Do I need Join Entities?

2) How can I create a relationship between the Responses and Stats table that would enable me to view the results for a specific query (i.e. responses given between two dates - e.g. dec 1 to dec 31, 2009)

3) Would I use Cross Tab Reports to compare results between the 3 populations? Can you use these types of reports if a response can have more than one value?

Hope this is clear. Any help would be much appreciated. Pulling my hair on this one!

Louise

[Matthew F]

I think you might be making this unnecessarily complicated. If you can keep the response data in a single flat file then it would be easier to use subsummaries to calculate totals means, and standard deviations. You could then script a find and sort that would display the data grouped as you have planned.

My approach would be to reserve the multi to multi relationship for the questionnaire template, and its related questions. I would create a separate flat table for the response data. A script could then be run when a new questionnaire is filled out that would copy over the relevant questions to the response table. You don't want users entering data on your questionaire template and you would benefit from easier stats with a simple response table.