I haven't used Filemaker in a while and I'm rusty but running up against a deadline. This may be a basic question but I haven't been able to find the answer in references yet.

I have a database (similar to a survey) with about 100 fields, each of which has a value list with 3 possible values. How can I get a summary or sub-summary that shows me how how many value1, value2 and value3 responses I have for each of the fields? Counting the fields isn't enough; I need to count the values within the field.

Thanks so much. M.S. Jackson

Surveys are one of those applications where databases are less than ideal. Purpose built survey programs just work better for structural reasons. It's kind of like the difference between spreadsheets and databases.

I'm going to assume that a record in your file is a person/survey and the fields are questions.

Method 1: Create one calculation field and one summary field for each response to a question.

Q1R1 (calc, number) = If(Question1 = "A", 1, 0)

Q1R2 (calc, number) = If(Question1 = "B", 1, 0)

Q1R3 (calc, number) = If(Question1 = "C", 1, 0)

CountQ1R1 (summary, Total of) = Total of "Q1R1)

CountQ1R2 (summary, Total of) = Total of "Q1R2)

CountQ1R3 (summary, Total of) = Total of "Q1R3)

This means creating about 600 additional fields. Once you have a summary field for each response to each question, you can put them in a trailing grand summary part and get totals.

Method 2: Use a relational structure with three files: Respondents, Questions, and a join file respondent/question. The join file has one entry for each respondent/question. Using this approach you only need one answer field. If you summarize you results from the join file, you can sort by question then answer with corresponding subsummaries. This will give you a line in the report "Question 1" followed by one line with a total for each response.

It is important to determine how you are going to get the data out BEFORE you pick a data structure for a database system.

-bd