You can use a summary field to compute aggregate values such as the total, average, standard deviation, count...
To get this for a specified set of records, you can perform a find or you can access the summary field from a related table where the relationship serves to link only to the sub set of records in question and then your summary field reports the aggregate of that set of related records.
I do not see how a related table could link to a "moving" set of records. For example, I would just like to have a field that is the average of this field over the last 7 records (days), but that last 7 day average should then be different on every record that I look at.
I said it could be used, not that it would be simple or a good idea in your case. A simple summary report is much easier to set up and very flexible to use. It can be done as you can set up relationship that match to a range of values instead of a single value by using inequalities. I suggested a second alternative that's much simpler for most reports of this type.
Using the summary report, you find "all records over the last 7 days" and then would sort those records accordingly to get the needed averages or other aggregate values.
but that last 7 day average should then be different on every record that I look at.
You'll need to explain in more detail how your data is organized before I can go into details or possibly suggest a different approach.
Let me clarify my original question. For a simple example, say I have a database that has a date field (date) and a number field (miles_run) and I have 365 records for the past year with this data, and about 75% of the days have the miles I ran that day and the other 25% just have a date entry and a blank entry on the days that I did not run.
I know I can do a find request for a given date range and a summary field (summary_miles_run) would give me the total or average or min or max etc of that found set of records, but that is not what I want to do.
I would like to make a field in each record that gives the total number of miles ran in the preceding 7 days- and that field would be in every record (and would have a differnt result on every day depending on how many times and how far I ran the prior 7 days).
That is all that I am trying to do (except use other intervals rather than just the prior 7 days, ie month etc).
Is the interval always the same or something the user would select?
A self join relationship can match to all records in the same date to 7 days ago interval and then either a sum function or a summary field (from the self join's table occurrence) will compute the 7 day total.
If your records are sorted so that the records in question are in a consistent order, you may also be able to use a calculation field with the getNthRecord function. This isn't always possible, but it is a method that does not require adding a relationship to your system.
Here's a relationship, that matches all records with the same date upto a user selected number of days in the past:
MileageTable::Date > PreviousRuns::Date AND
MileageTable::cDate2 < PreviousRuns::Date
Define a global field, gDaysPast and then define cDate2 as a calculation field that returns a date:
Date - gDaysPast (if you want the interval to always be 7 days, just use Date - 7 and don't use the global field.)
PreviousRuns is an additional table occurrence of MileageTable to make this a self join.
You can then create a report layout based on MileageTable and put summary fields from PreviousRun on your layout. By specifying different values in gDaysPast, you get matches to different numbers of related records and the summary fiels report aggregate values (totals, averages, etc) of the resulting set of related records.
Thanks very much for your help. We will see if I can get this to work.
The records are always in sorted order by date (and also by record number).
I do not need the interval to be user selected. I would like to program that into the field.