What if a student has only one or zero entries in the Scholarships table? Should they be included in this report?
Sorry, left out a step.
The user will first perform a find of students who have valid data (i.e. find all students who have a record in Fall 2009).
Given that, zero entries will not be an issue. Single entries, however will exist for all first-year students and should be included in the report.
Well, there are several ways that come to mind, none of them too easy (or fast). The main difficulty here is the "one-line per student" requirement. This means that (1) you must export from the Students table, and (2) you must add some calculation fields to this table.
Anyway, if you sort the relationship so that the Scholarships records are in descending chronological order (I assume you know how to do this), you can use the GetNthRecord() function to retrieve data from the first two related records, e.g.:
GetNthRecord ( Scholarships::Amount ; 2 )
will return the amount of the student's second most recent scholarship (this calculation must be unstored).
You are correct, it was not a fast solution, but it was in fact a solution that worked!
I didn't even consider that calculation.
For those playing at home, I had to create 8 non-stored calculations to make this work (4 fields with first and second occurrences).
After getting it all together, I even located some previously unknown data entry errors (I know, not really that shocking in a multi-user environment).
Thank you for the excellent suggestion!