I'm using aggregate functions to calculate the Sum, Average, Standard deviation etc. of some items in a related table.
I have two tables, Items and Summaries
The Items table has 5 fields: Key_ProcessYear, Input_Item1, Input_Item2, Input_Item3 and Input_Item4
The summaries table has 11 fields: Key_ProcessYear, Sum_Item1, StDev_Item1 ... Sum_ItemTotal, StDev_ItemTotal.
I then have a relationship based on Key_ProcessYear
Calculating the Summaries for each item through the relations works fine.
Calculating the StDev for each item through the relation also works fine.
As I suspect you all know, doing something like Sum( Items::Input_Item1 ; Items::Input_Item2 ; Items::Input_Item3 ; Items::Input_Item4 ) does not give me the sum of Summaries::Sum_Item1 + Summaries::Sum_Item2 + Summaries::Sum_Item3 + Summaries::Sum_Item4.
It actually returns Items::Input_Item1 + Items::Input_Item2 + Items::Input_Item3 + Items::Input_Item4.
I know I can write a script to perform the calculation and run it everytime needed.
That is everytime I display the layout containing the aggregate results and everytime I change a value in the items table if a layout showing calculations based on the edited value is being displayed...
But is there really no simpler solution?