finding across multiple fields and totaling

[susan]

Working again in my inherited database (too late to change the structure)...

I have fields for 10 teachers (Teacher1...Teacher10) (Actually, the teachers are stored in a related file). For each record (student), Teacher X may be in any of the 10 teacher fields, or she may not be on that record at all. Each teacher keeps track of how many minutes she spends with each student.

I have a report that gives me all of the students for a given teacher, the time spent on that student, and the total time spent by that teacher.

Now I need a report that lists all the teachers and their total time spent, regardless of the student.

How do I run through my entire database, pull out each teacher individually, total up their time spent, and then list all the teachers together? I'm pretty sure I have to use a Loop to do the math, and maybe one to find the teachers, but I can't get a handle on it. Any help would really be appreciated.

Susan

[Vaughan]

Ahhh Susan. The horse is dead, no good flogging it.

Time to re-build the database properly using related fields. Each fix that you make to overcome the current database's shortcomings is only time wasted on doing the job properly. The fixes will get longer and more complicated and start to beak.

[Rigsby]

I don’t know if this is going to help you – as it’s not really a direct answer to your question (You should read my comments on Rolling Total Amounts). But maybe you really should consider getting the different pieces of information into different files. Maybe you already know this, but Filemaker has some pretty cool tricks up its sleeve for dealing with repeating fields.

I also inherited a database with just about all the information stored in one file. One problem was an activity page, where all telephone conversations with customers were entered in chronological order into a series of 50 repeating fields. This had the following disadvantages:

After 50 entries, the company had to change the field repetition values to allow for more entries, which of course no longer fit on the layout.

After a crash, Filemaker is more likely to lose repeating field data than anything else.

Creating reports is very difficult (Your problem)

So! Just use the brilliant import utility from Filemaker and import the teachers into a new database, but splitting repeating fields into single records.

If you then use relationships specific to

One pupil one teacher

One teacher

One pupil

Etc., etc, you can pretty much filter any statistic you want.

One very big tip here with inherited databases, where basically everything is in one file, is to make several copies of the file and rename the copies – then create your relationships, deleting no-longer wanted fields or information in each file, then using each file to store individual pieces of information.

In other words – starting from scratch doesn’t always mean starting from scratch. For example, I used this technique to create a backup file of company addresses:

We had the problem, that inexperienced users sometimes deleted important information, a street name or company name for example. Here I created one unique field (Company ID), which no one can change, duplicated the whole file, related it to the original file, and used a relationship to give a warning if the standard information in file 1 doesn’t mach file 2. This involved some serious script work for creating new records, but since then has saved us a lot of problems. And if a company moves or changes its name, Filemaker asks if the new info should be stored. The trick here is then to create a new backup file, and set the old one to “Archived” thus old addresses and company names don’t find their way back into the system if we import from CDs etc.

So, think about splitting……. I’ll help you any way I can.