Clearly you have a relationship linking a parent record to Giving_History and another linking it to Membership_History.
What do those relationship look like?
You can modify these relationships or define new ones that include date fields that you can use to specify a date range such as 7/1/2009...6/30/2010.
Post your relationships and we can give you a more detailed example of how to do it.
Hi. I have 3 tables:
Membership_History <--- Membership ---> Giving_History
all 3 tables have a field called "member_ID". The "member_ID" in "Membership" is a serial number. They are all linked through this field.
I hope my description makes sense...
In otherwords, you have:
Membership::member_ID = Giving_History::member_ID
Membership::member_ID = Membership_History::member_ID
In your membership table, define two new date fields, gYearStart, gYearEnd and select the Global Storage option in field options.
Now a multiple field relationship like this:
Membership::member_ID = Giving_History::member_ID AND
Membership::gYearStart < Giving_History::date AND
Membership::gYearEnd > Giving_History::date
Will give you what you need. You simply have to enter or select the desired dates for the beginning and end of your fiscal year.
Repeat this same set up for membership_History.
You may need to these set up as separate relationships if you need the above relationships unchaged for other uses. In that case, select your two history tables in Manage | Database | Relationships (actually they're table occurrences) and press the button with two plus signs to make new table occurrences. Use them to define your relationship and refer to these new table occurrences in your calculation.
Thanks! That worked perfectly. Thanks for showing me how to post relationships too. I'm sure that will come in handy.