2 Replies Latest reply on Jan 13, 2010 12:16 PM by philmodjunk

# Statistical Analysis of Questionnaire Results

### Title

Statistical Analysis of Questionnaire Results

### Post

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

• ###### 1. Re: Statistical Analysis of Questionnaire Results

I understand that a user can choose more that one answer in response to a question - but it's not clear how you intend to treat those in your statistical analysis.

• If  a question can belong to more than one questionnaire, than properly there should be a join table in between them (though in some cases you can get by entering multiple values into the foreign key field).

• I don't see a table of Choices (i.e. the available answers users can choose from).

• I am not sure what the Stats table is. You should keep in mind that Filemaker doesn't do crosstabs natively. The default way to get stats is to produce a report (in this case, from the Responses table) with sub-summaries. This produces stats on-screen or in print - but the summary values are not kept anywhere.

• ###### 2. Re: Statistical Analysis of Questionnaire Results

I'm just sharing a few possible techniques here not the full solution:

There's a relationship trick that may nicely handle your "Multivalued" reponse issue. I interpret that as having a check box formatted field where respondants have clicked more than one check box. Such fields can be used in a special "multivalue relationship" that matches any of the selected values. (This works in Filemaker because each checkbox value stored in a field is separated by a carriage return.)

It works like this:

Table::SingleValueField = RelatedTable::MultiValueField

If SingleValueField = "Red", it will match records in the related table where MultiValueField = "Red¶Blue¶Green", "Green¶Red" or just "Red"  (¶ is the character for a carriage return).

You have aggregate functions that should give you the desired statistical results such as sum, count, average, StDev, etc. just like summary fields.

Thus, A calculation field defined in Table as Count(RelatedTable::MultiValueField) will count the number of responces that contain a value matching the current record in Table.

"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)"

The most flexible approach here is to simply perform a find (in script or by hand) that pulls up records matching your criteria. Another option that may work for you is to use a filtered portal where the criteria is entered into a field that's part of the relationship linking the portal's table occurrence.

You can search this forum under Filtered Portal to get some good threads on that trick.

Hope that at least gets you thinking in some new directions.