Newbie in need of help!

[David Langford]

I've built a database in FMP4.0 to record travel agents to which we send product on behalf of our clients.

Currently there are over 5,000 records. Each record has a layout with 15 fields indicating our clients and 15 corresponding fields which categorize the agent as "A", "B", "C"..."F" according to their importance to our clients.

I need to create a summary layout which returns a count of each category by client (eg: Client One: 15 A, 29 B, 987 C, etc.).

One way I can do this is create a series of "if" calculations eg: if(client one category = "A", 1,0). Then perform a count of the results. However if I do this, I'll need create 180 calculations (15 x 6 categoies and 15 x 6 counts).

Can anyone show me a better, more efficient way to do this please?

TIA

David

[LiveOak]

It's a little late, but if you had used a related file and made each record a client/question, adding 15 records with six possible responses each for each complete questionaire, it would be a lot easier. You would only have 6 calculation fields and 6 summary field (OBTW use Total of, NOT Count of). The category would be a field with a selection.

I suppose you could write a script to process all 5000 records into 75,000 records in a new related file. Or just continue on and create the 90 calc fields and 90 summary fields.

-bd

[Vaughan]

I'm in the middle of some development work (the reason I've been quite on the Forums lately) part of which is converting flat-file to relational... exactly waht you've gotta do, David.

Writing a "converter" file took about half a day: it has a script that looks at each field and if it has a value it creates a new related record and populates it with the appropriate data. It works miracles! I import into the converter db, run the script and fifteen minutes later it's done!

Just think out the new data structure carefully before-hand, and go slowly. Even if you spend a week on the converter, it'll save you months later on. Oh, my best advice: document as you go; and backup, backup, backup!

The cool thing is, once I got the converter to work, I've been able to adapt it to several other tasks as well. *That's* where the value is.

Vaughan