Total of Last Year's Revenue

[jrp]

I'm not at all sure where this question belongs, so I'm going to try relationships.

I have 2 files:

Reps

Districts

I need to do a monthly import of revenue data from an Excel file containing three fields: RepID, DistrictID, and Revenue. Most line items on this file will contain both a District ID and a Rep ID, but a few line items will only contain one or the other. I need that data to be available to both the Reps file and the Districts file, and I need to be able to summarize revenue for both Reps and Districts, based on Last Year's Total, and Current Year to Date.

I created a third file, called Rev_LI (revenue line items) into which I import these files. I created a script to assign each line item, on import, a month and year. I created relationships from the Reps file and the Districts file to the Rev_LI file. But now I'm stuck. I can't figure out how to summarize the data into "Last Year's Total" and "Current Year to Date" on either the Reps file or the Districts file.

Can anyone suggest a method? Would be so grateful....

jrp