Sum of field of a field in 2 different subform.

[calvinle]

Hi,

I am trying to create a simple budget planner.
So far, I have the following table:
tbl_account [id|acc_type|acc_name|balance|memo]
tbl_expense [id|exp_date|exp_amt|exp_acct|exp_payee|exp_type]
tbl_expense_cat [id|cat_name|cat_type]
tbl_income [id|inc_date|inc_amt|inc_acct|inc_payee|inc_type]
tbl_income_cat [id|cat_name|cat_type]

The relation now are:
[tbl_account:id] ---> [tbl_income:exp_acct] --> [tbl_income:id]
|_______> [tbl_expense:inc_acct] --> [tbl_income:id]

I have 3 subform on the main form:
[account_sf]
[expense_sf]
[income_sf]

I have set all them to continuous. Now I have added 3 account in the tbl_account.

I wonder how to SUM all the income of certain account, and minus all expense of certain account, and have them showed in the subform tbl_account.

Here is the attach pics:


Thanks.

 

[vbaInet]

I would have expected all income to be positive(+) and all expense to be negative(-), but I'll go with what you currently have.

1. Drop a textbox in the footer section of the Income and Expense subforms, and enter the following in its Control Source:

=Sum(IIF(Account="CL_CIBC_MC", [Amount], Null))

... call them txtSumIncome and txtSumExpense respectively.

2. Enter one of the following expressions in your Calvin's Mastercard textbox:

=Nz([Form]![[COLOR=Blue]IncomeSubformControlName[/COLOR]]![txtSumIncome], 0) - Nz([Form]![[COLOR=Blue]ExpenseSubformControlName[/COLOR]]![txtSumExpense], 0)

=Nz([Forms]![[COLOR=Blue]MainFormName[/COLOR]]![[COLOR=Blue]IncomeSubformControlName[/COLOR]]![txtSumIncome], 0) - Nz([Forms]![[COLOR=Blue]MainFormName[/COLOR]]![[COLOR=Blue]ExpenseSubformControlName[/COLOR]]![txtSumExpense], 0)

... amend the bits in blue. You can even use the Expression Builder to locate both textboxes.

 

[calvinle]

Thanks for the info.!
However, I am wondering if there is another way. As you can see, I mentionned that there was 3 subform. 1 for expense, 1 for income and 1 for account. The subform account is continuous, and it list all the account that I have set, or I can even add more.
In the exp_acct and inc_acct, of tbl_expense and tbl_income respectitively, they contain the data of acc_type of my tbl_account in comboxbox.

Since the account_sf is continuous, everytime I add new account to the table, it will automatically create that account in the subform.

By doing the way you suggest, I will have to add a new textbox for every single account, right?

Is there a way to setup so the sum will add to each of the account in the subform without having to create individual textbox?

Thanks.!

 

[vbaInet]

Right! So how are the subforms filtered? I.e. are they linked to the parent form and is the user also allowed to apply a filter on them?

 

[calvinle]

For the account_sf, the user cannot filter them, as they are the main account.
For the exp and income, yes they are allowed, however it should not change the total in the account subform. The filtered are there to minimize the data in the subform of expense and income but should not affecte the account total balance.

 

[vbaInet]

When I say filter, I'm talking about the filter options that are available on the ribbon. Are these disabled? If they are not then there's nothing stopping the user from selecting the ID and filtering it down. Confirm this.

 

[calvinle]

Does it make any difference? As I know, the ribbon will be disable yes.