Sum from two different tables

[Guest]

Hello.
I need to create a report that sums currency fields in two different tables.
Table tbl_Income has a currency field called Amount
Table tbl_Expense has a currency field called Amount.
I need to create a report kind of like this:

Total Income
Total Expenses

Balance (Total Income minus Total Expenses)


I would like to sum all of the income fields in table tbl_Income and put it
into the report field "Total Income" and the same for "Total Expenses" then
do a "Balance Field".
Could you help?

Thanks.
Iram/mcp

 

[Allen Browne]

If you want all the income listed first and the expenses afterwards, you
could create a report bound to tbl_Income. Then add a subreport bound to
tbl_Expense. To get the balance, see:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

If you want to merge the 2 lists so the income and expenses show in date
order, you could use a UNION query to merge them. This kind of thing:

SELECT tbl_Income.IncomeDate AS TransactionDate,
tbl_Income.Amount AS IncomeAmount,
IIf(True, Null, 0) AS ExpenseAmount
FROM tbl_Income
UNION ALL
SELECT tbl_Expense.ExpenseDate AS TransactionDate,
IIf(True, Null, 0) AS IncomeAmount
tbl_Expense .Amount AS ExpenseAmount
FROM tbl_Expense;

(Why the odd-looking IIf() expression? If you just use Null, JET treats the
column as Text (or binary), not numeric. The expression always returns Null
becuase True is always True, but the presence of an alternative is enough to
indicate a numeric data type.)

 

[Guest]

Thanks for the information Allen.
Actually all I need is the sums of both tables without the showing all the
income or the expenses.
Is there something like "=Sum([Amount]) from tbl_Income" or "=Sum([Amount])
from tbl_Expense" commands like there is for =dCount?

Iram

 

[Allen Browne]

Try DSum().

This kind of thing:
=Nz(DSum("Amount", "tbl_Income"),0)
- Nz(DSum("Amount", "tbl_Expense"),0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the information Allen.
Actually all I need is the sums of both tables without the showing all the
income or the expenses.
Is there something like "=Sum([Amount]) from tbl_Income" or
"=Sum([Amount])
from tbl_Expense" commands like there is for =dCount?

Iram

If you want all the income listed first and the expenses afterwards, you
could create a report bound to tbl_Income. Then add a subreport bound to
tbl_Expense. To get the balance, see:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

If you want to merge the 2 lists so the income and expenses show in date
order, you could use a UNION query to merge them. This kind of thing:

SELECT tbl_Income.IncomeDate AS TransactionDate,
tbl_Income.Amount AS IncomeAmount,
IIf(True, Null, 0) AS ExpenseAmount
FROM tbl_Income
UNION ALL
SELECT tbl_Expense.ExpenseDate AS TransactionDate,
IIf(True, Null, 0) AS IncomeAmount
tbl_Expense .Amount AS ExpenseAmount
FROM tbl_Expense;

(Why the odd-looking IIf() expression? If you just use Null, JET treats
the
column as Text (or binary), not numeric. The expression always returns
Null
becuase True is always True, but the presence of an alternative is enough
to
indicate a numeric data type.)

 

[Guest]

Perfect!
By the way what is Nz and ,0?

You are the MAN.

Try DSum().

This kind of thing:
=Nz(DSum("Amount", "tbl_Income"),0)
- Nz(DSum("Amount", "tbl_Expense"),0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the information Allen.
Actually all I need is the sums of both tables without the showing all the
income or the expenses.
Is there something like "=Sum([Amount]) from tbl_Income" or
"=Sum([Amount])
from tbl_Expense" commands like there is for =dCount?

Iram

If you want all the income listed first and the expenses afterwards, you
could create a report bound to tbl_Income. Then add a subreport bound to
tbl_Expense. To get the balance, see:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

If you want to merge the 2 lists so the income and expenses show in date
order, you could use a UNION query to merge them. This kind of thing:

SELECT tbl_Income.IncomeDate AS TransactionDate,
tbl_Income.Amount AS IncomeAmount,
IIf(True, Null, 0) AS ExpenseAmount
FROM tbl_Income
UNION ALL
SELECT tbl_Expense.ExpenseDate AS TransactionDate,
IIf(True, Null, 0) AS IncomeAmount
tbl_Expense .Amount AS ExpenseAmount
FROM tbl_Expense;

(Why the odd-looking IIf() expression? If you just use Null, JET treats
the
column as Text (or binary), not numeric. The expression always returns
Null
becuase True is always True, but the presence of an alternative is enough
to
indicate a numeric data type.)

Hello.
I need to create a report that sums currency fields in two different
tables.
Table tbl_Income has a currency field called Amount
Table tbl_Expense has a currency field called Amount.
I need to create a report kind of like this:

Total Income
Total Expenses

Balance (Total Income minus Total Expenses)


I would like to sum all of the income fields in table tbl_Income and
put
it
into the report field "Total Income" and the same for "Total Expenses"
then
do a "Balance Field".
Could you help?

 

[Allen Browne]

If there are no matching records, the domain aggregate functions return
Null. So if there are income records only, but no expenses, the expression
could return:
100 + Null
which results in Null. The Nz() converts the null to the value of the 2nd
argument. That's zero in this case, so the expression would become:
100 + 0
which is 100.

See help on Nz().

There are half a dozen of these kinds of issues that people keep snagging
themselves on. You can get on top of them with:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Perfect!
By the way what is Nz and ,0?

You are the MAN.

Try DSum().

This kind of thing:
=Nz(DSum("Amount", "tbl_Income"),0)
- Nz(DSum("Amount", "tbl_Expense"),0)

Thanks for the information Allen.
Actually all I need is the sums of both tables without the showing all
the
income or the expenses.
Is there something like "=Sum([Amount]) from tbl_Income" or
"=Sum([Amount])
from tbl_Expense" commands like there is for =dCount?

Iram

 

[Guest]

It worked.

Thanks.

Again, you the man.

Iram/mcp

If there are no matching records, the domain aggregate functions return
Null. So if there are income records only, but no expenses, the expression
could return:
100 + Null
which results in Null. The Nz() converts the null to the value of the 2nd
argument. That's zero in this case, so the expression would become:
100 + 0
which is 100.

See help on Nz().

There are half a dozen of these kinds of issues that people keep snagging
themselves on. You can get on top of them with:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Perfect!
By the way what is Nz and ,0?

You are the MAN.

Try DSum().

This kind of thing:
=Nz(DSum("Amount", "tbl_Income"),0)
- Nz(DSum("Amount", "tbl_Expense"),0)

Thanks for the information Allen.
Actually all I need is the sums of both tables without the showing all
the
income or the expenses.
Is there something like "=Sum([Amount]) from tbl_Income" or
"=Sum([Amount])
from tbl_Expense" commands like there is for =dCount?

Iram