I am having difficulty with a calculation and I am not even sure if it is possible to do.
I have the following tables.
There is a "global_date field" in the ACOUNT table that grabs the current month. It is used to form a relationship between the ACCOUNT table and the MONTH table. The ACCOUNT table also has the field "Account Balance" which is used to store the actual current account balance for each account record.
Indvidual Budgets are related by a "month" field to the Month table. The Budget table has the field "Amount of Budget Remaining" for each budget record. A calculation global field, "global_Total Amounts Remaining," in the Month table totals the amount of Budgets Remaing for each month.
What I would like to do is create the field "Available Account Balance," in the Account table that calculates the available balance for each account record by summing the "Account Balance" field for all account records and subtracting out the "Total Amounts Remaining."
Is this even possible using a calculation?
I was thinking of adding a "Rank" field for each account record so that one account would be the priority and if the "Available Account Balance" for that account was less than 0 then the account with the next ranking of 2 would pick up with the remaining difference and so on until all of the "global_Total Amounts Remaining" for that particular month would be accounted for.
Some functions that came to mind as useful are the Case, Let, & Evaluate. However, I cannot think of a good way to do this.
Any assitance or suggestions for a better way to approach this is appreciated.