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

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? :)

I modified the Topic to make it easier to read.

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+.