Newbies David Langford Posted August 2, 2001 Newbies Share Posted August 2, 2001 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 Link to comment Share on other sites More sharing options...
LiveOak Posted August 2, 2001 Share Posted August 2, 2001 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 Link to comment Share on other sites More sharing options...
Vaughan Posted August 2, 2001 Share Posted August 2, 2001 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 8493 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now