Survey summary

[Karma]

I am trying to make a database that will summarise surveys.

Each question has the answers:

strongly disagree

disagree

agree

strongly agree

no onpinion

I want to know how can i summarise the answers?

i.e 5 Strongly disagree, 10 agree and 3 no opinion?

I need to be able to do this for about 22 questions.

Hope someone can help...

[richMac]

Karma,

I assume from your description that there is only one answer for each question. In that case, use a Boolean function to get a numerical value for each answer on each record. If your answer field with the value list is called Answer, create a new calculated field named StronglyDisagree with a number result with the function Answer="Stong Disagree". It will look like

StonglyDisagree (Calculation, number)=Answer="StronglyDisagree"

If true, in other words if the answer is "Strongly Disagree", then a 1 is returned. If not true then 0 is returned. Create a calculation like this for each value in your value list.

Boolean functions have the advantage of being much faster that IF statements and should not affect the performance of your database.

Then create a Summary field for StonglyDisagree, a Summary field for Disagree and so on. The Summary field will total the 1 for the positive response for each value in each record and give you the totals. Place these Summary fields in a Summary part of your layout, label them and you have your report.

Trust you can take it from there.

Happy FileMaking!

[Ender]

If the survey is all in one table, then using richMac's method you would need five calc fields for each question, or 110 calculations. If this doesn't appeal to you, you can try using a GetField() calc with sub-summary parts instead:

gSelectedQuestion (global, text) //this is for the name of the field that is to be summarized

QuestionToCheck (calc, text result) = GetField(gSelectedQuestion) //This will show the Answer for whichever question was entered in the gSelectedQuestion field.

CountOfRecords (summary) = Count of RecordID

On a list layout, use a Sub-Summary by QuestionToCheck part instead of a Body part, and insert fields QuestionToCheck, and CountOfRecords. On the Header, insert gSelectedQuestion.

Now when a question's field name is inserted into the gSelectedQuestion field, the records are sorted by QuestionToCheck, and the layout is viewed in Preview Mode, you will see the results for that question summarized.

(If you were looking for a way to view the summary results of ALL questions on one layout, the algorithm is more complex, but it can be done without adding 110 fields.)

[Ender]

If the survey is all in one table, then using richMac's method you would need five calc fields for each question, or 110 calculations.

I forgot the 110 summary fields you would need too.

[The Shadow]

Another way to reduce the fields is just to have a single calculation with 5 repetitions, it would use Get(CalculationRepetitionNumber) in a Case() to contain all of richMac's boolean expressions.

Then make a single summary field, and choose "summarize by repetition", and you'll have a summary field with 5 repetitions that contain the totals.

[kraftyman]

I don't think you can get away without lots of fields IF you leave it all to filemaker. FMP was never designed to be a statistics tool. The easiest thing is to get the data out to an Excel worksheet and do your analysis there.

If you have 22 fields, then presumably you will want a mean score with variance or standard deviation, maybe a count of valid entries as well. This means at least 3-5 summary fields per data field. ( you could have repeating fields for this, but the essential problem of multiple fields is still the same). Excel is MUCH better at this kind of statistics than FMP.

The best thing in my opinion is to have the data stored in FMP, then pull it into Excel via an ODBC link. Then you can get pivot charts, the works....

For the man with only a hammer, every job looks like a nail

[Deep Thought II]

i have a way that u can do the summarizing with only 2 fields, but the pre-requisite is that ur selections are only in 1 field using a value list or something. then there is a value field that only contains values 0 and 1. regardless which selection is selected, as long as the user makes a selection, it auto-enters 1.

then the summary part,

1st field - create a summary field that totals value field (not running total).

2nd field - create a calculation field that evoke the GetSummary function with Selection as its break field, and the 1st field as the summary field.

then the value that u want is just in the 2nd field.

p.s. u have to sort ur records by the Selection field before GetSummary can work, i think...

[Deep Thought II]

i got yet another way... such that u need only 1 field! ok the pre-requisite is the same as my last post. ur selections are in 1 "Selection" field, then a Value field that has only 0 and 1's.

1st field - create a summary that totals the value field (not running total).

then in the relationship graph, create a copy of the same table and relate it to the original with the field selection. sort the copied table by "Selection", and then create a layout from the copy table, insert the summarizing field from the original table. and it's automatically done for u the summary for each selection.

[Ender]

Deep Thought II,

I can't follow your description. Maybe you could post an example that accomodates Karma's requirements.

[Deep Thought II]

ok.. for example, there are fields "Name", "Region", and "Salary." The data in each field is just as the name suggests, and Region certainly has multiple instances. Now, to find out the salary that's earned in each region, one only need to create a summary field, say, "Sum", which totals Salary.

Then in the relationship graph, create a second instance of the same table, and make a relationship to its first instance with fields Region and Salary. Sort the table by Region.

Then make a layout based on the second table, and drag the field Region to the table. Then drag Sum to the layout from the first table.

Then you will have the sum of salary by each region.

There is one problem though... the region field will appear as many times as it was entered, but the sum remains the same for each region in its multiple instances. There should be a little workaround to fix this...

Or there is another way to get the subsummary, which is just the GetSummary function.