I am building a budget database and have the following relationships.
As you can see the CY Table is linked to the Budget Table from ID_CY (1 to Many) FK_CY. I have a field in the Budget table called Sequence (Number) that I want to filter for a particular number (1 for example) and sum the field (Jan_Budget) from the results. Here are the fields for each table.
As I understand it, the parent table (CY) must have a calculated field to perform this task. As you can seem I have them summed but it gives me the sum for all the records instead of the filtered records.
On my layout, I have separate portals that filters each of my sequence numbers (1-5) for each of the following:
Sequence 1 - Money In
Sequence 2 - Investments
Sequence 3 - Liabilities
Sequence 4 - Credit Cards
Sequence 5 - Expenses
Ultimately, I was hoping to perform the following bottom line calculation that will give me a Remaining Balance.
(Money In - Investments - Liabilities - Credit Cards - Expenses = Remaining Balance)
I hope that I have explained this well enough to make what I want to do clear. Your help in this matter is greatly appreciated.