See attached. The primary relationships are represented in the top row. Some of the other relationships are experimental. The bottom column of A_Person/Study/Date_n are used to show summaries in portals of Person/Study/Date on the Centre layout.
I am trying to sum Activities. These are from the Activity::Activity field. Eg. I have a Date::Strategy calc field using:
ExecuteSQL( "SELECT SUM(\"Time\") FROM Activity WHERE Activity = 'Strategy' AND \"_Date_uuID\" = ?";"";"";A_Centre::Date_n)
to sum the amount of time used for Strategy on a specific date. This appears in the Date_n::Strategy field within the Date_n portal on the Centre layout.
I should then like to sum activities for Dates within each Study and Studies for each Person, Persons for each Centre. I have tried using a simple 'Sum(field)' based upon relationships and well as SQL. I end up getting either '?' or double whatever Date is in focus. Obviously I'm missing something in what appears a simple exercise. Some pointers would be appreciated.