I have multiple tables/forms that are used to record expenses (Different type of expenses hence the multiple tables). Each of these tables has a total field that is usually rate x qty but also it could be rate x qty + amount1 + amount2 and so on.
These tables are working fine and the total formula is working with no issue.
The problem is that I'm trying to have another table that captures only the SUM of total fields from these multiple tables.
For example:
Table "Food Expenses" can have multiple items (records) and each one has a total.
3 Breakfast x 3 people x $10 per person = Total will be $90
2 Dinners x 3 people x $10 per person = Total will be $60
The summary of the Food Expense table is working just fine (In this case it's $150)
Now on the Budget Table, I need to be able to SUM all these Food Totals from above based on a connection (Budget Table Account No. is Equal to Food Expenses Account No.)
So I need the Account Number for the Food Expenses in the Budget Table to be $150 and also when I edit any of the expenses records in the Food Expenses Table I need the Budget Table Total to update of course.
Thanks in advance for your help!