Summarize Fields Across Different Files

[Matthew R White]

My files are organized by number, each year that they are a client. Ex: First year the file has a 1, second year the file has a 2. I also have a field called total of all charges, which is linked to the file number. Whats the best/easiest way to summarize these fields across multiple file numbers?

thanks

[LaRetta]

It is amazing that someone as young as yourself types in all caps. In most online venues, it is considered shouting or rude but I just find it very difficult to read.

As to your question about summarizing across files ... the data should reside in same table with a Year field (preferably just a date field). There are reasons for splitting some data (archiving older records for example) but not for each year. No data-modelling or trend-predictions will ever be possible the way you have it.

You will also need to remember to modify many calculations (and probably scripts) whenever you add a new file (relationship year). There are several other problems you will face with this type of structure; this current issue being one of the least. Can you explain why you feel the data should be split, not only into different tables within a file but also in different files? :)

[Lee Smith]

I modified the Topic to make it easier to read.

[Matthew R White]

In the insurance industry you must keep old records for at least 5 years. In our case, each year we create an entirely new record, basically a modified duplicate of the customers previous file. We start out at year one, file number 1 for the customer, if that customer renews his insurance next year, we create a duplicate file, change the number to 2, and modify all information so that its current. In these records each year is a number field that calculates and totals the customers charges for that year. I am trying to add these fields up over the course of the customers history with us, some customers have 1-2 records, some have 20+.