The attached image 1 shows the form used to display transaction information from a stock portfolio filtered according to the Security Symbol, Activity Description and Date interval. The form is a standard filtered form, what makes it different is the addition of calculate fields.
Here the calculated fields are the sum of the values returned in the sub-form columns Quantity, CAD_Amount and USD_Amount. Searching the internet on how to do that did not provide a solution, so this is my version of a possible solution. Anyone with a better suggestion, I would very much appreciate knowing about it.
Once I stopped locking for a silver bullet (a form object that would automatically allow aggregation of a specified field in the sub-form), the solution came as a work around.
I created a Filtered_Query_Table (Attached image 2) where the Data_Table and the Filter_Table are linked through the filter fields of the Filter_Table.
Note: The link for the FromDate and ToDate are invisible since those two fields cannot be aggregated
Then I included a second form in the filtered form linked to the Filtered_Query_Table and added to this new form formattedfieds with their data entry being each of the aggregated fields of the Filtered_Query_Table.
This works fine as long as non of the filter fields in the filtered form, are left blank. A blank value in any of the filter field will return all the values of that field in the Data_Table as if no filter was set for the field. But, in the Filtered_Query_Table, the field will be set to blank thereby returning no results.
Though it would be nice at times to have, for example, all securities sold in a given year and aggregate the amounts for each security in one form for comparison, I have not yet been able to do so on a filtered form. Any suggestions?
P.S. I haven’t been able to either insert or attach the images mentioned above. Sorry.