Trying to reduce the number of Calculation fields!

[Micah DJ]

What I'm working with, is a set of 16 mulitple choice survey questions.

There are five possible answers each and I'm storing the values as a single numerical field

My problem is, I want to do some statistics on this set of data, so I'm trying to find the simplest way to sort the data into the five possible values.

The way things are currently calculating, I'm essentially creating five IF statement fields on each question that check the value of the answer (1 to 5) and if it matches that particular if statement, then that field contains a 1. Then I've got five more summary fields giving me a count of those if statements so I can get an accurate count of each answer.

To make matters worse, I have all the questions in the same database.. it's near 400 fields in total.

(No I didn't create it, I'm just trying to fix it)

I have an SurveyID field that relates the question databases to the answer database.

Is it possible to create a complex join in FM Pro? (i.e. Relate on this field in this table(db) AND this field in this table(db).

If so.. I can at least pull each answer onto a seperate record and relate by both SurveyID and QuestionNo.

Is there an easier way to store and work with ordinal data types? Any pointers in the right direction would be greatly appreciated.

TIA

Micah

[CobaltSky]

Hello Micah,

It sounds as though you have all sixteen answers in separate fields on the same record, probably along with details about who the respondent was etc. This is essentially a flat file structure which is necessarily inefficient.

I suggest that you go relational and create two separate files - one for the respondents and a related file for their answers with each answer on a differnt record.

That way you will be able to use a single set of calcs to break out the answers (rather than sixteen) and you'll be able to use relationship filtering to get summary data about all the answers to a particular question.

[Micah DJ]

Thanks Ray,

That's essentially the direction I started out in. Where I got stuck with it though is that I have multiple sets of 16 questions being stored in the same database.. I I'll have both QuestionNumber and SurveyID as key fields. Is it possible to do a compound relationship in FM Pro 5.5? (I.E. Relate on QuestionNumber AND SurveyID at the same time?) Or do I need to concentrate on one relationship and try to make the rest of it work with found sets?

Thanks in advance.

Micah