I don't know whether my post title is representative of what I am trying to accomplish. But here goes.
I have a BUDGET TABLE and a O/M month/year relationship with an EXPENSE TABLE.
In the EXPENSE TABLE I have a text field (PURCHASE LABEL) with a self defined value list. Example: Office, Fuel, etc.. I also have a corresponding number field (AMOUNT SPENT) representing the amount spent on one of the different (PURCHASE LABEL) values. I have a corresponding text field (PURCHASE LABEL) in the BUDGET TABLE. The (PURCHASE LABEL) values in the BUDGET TABLE are pulled from the (PURCHASE LABEL) values created in the EXPENSE TABLE.
I created a sum calculation in the EXPENSE TABLE to sum the values of the (AMOUNT SPENT) field. What I'm having a hard time with is summing the (AMOUNT SPENT) based upon the unique (PURCHASE LABEL) value and then filtering it down based upon the TRANSACTION DATE field.
In other words if I made 2 purchases for 200.00 and 300.00 respectively under the (PURCHASE LABEL) Office and 3 purchases for 60.00, 70.00 and 80.00 respectively under the (PURCHASE LABEL) Fuel, I only want to sum the 2 Office purchase amounts (500.00) and sum the 3 Fuel purchase amounts (210.00) within the NOVEMBER 2013 date range. Those amounts would be brought over into the budget table and into the NOVEMBER 2013 budget record and depending on which Budget (PURCHASE LABEL) value is selected, the corresponding summed amounts (500.00 for Office) or (120.00 for Fuel) would be subtracted from the corresponding NOVEMBER 2013 (BUDGET AMOUNT). The result would be the remaing budget amount that could be spent for OFFICE items or FUEL within the NOVEMBER 2013 date range.
I tried different portal filtering and calculations but I still keep getting weird totals in the sum (AMOUNT SPENT) field that don't make any sense or the value doesn't change when going from one BUDGET record month to the next. The BUDGET AMOUNT - sum (AMOUNT SPENT) result seems to work. The label relationship seems to work as well in that there is information loading into the sum (AMOUNT SPENT) field when the BUDGET LABEL = the EXPENSE LABEL. The numbers are wrong in the SUM (AMOUNT SPENT) field.
Thanks for any direction or ideas.