How do I show net profit on income statement

[Guest]

Using Access Reports and views. I already managed to show:
1. the income amount for each income account
2. the total income using a group footer sum.
3. the expense amount for each expense account.
4. the total expense using a group footer sum.

I could do those because my tables and views merged into a view in which the
income amounts and expense amounts and account names are the same columns
whether income or expense.

But now I am stuck on how to show net profit. I wish I knew how to follow a
report with another report that uses another table. But I only know how to
use one table or query in a report. Can a report use two or more tables
(queries)?

Or another way I might get the net income would be if I could somehow sum
positive the income and sum-negative the expense.

Are there any tips to get me in a good direction?

 

[Larry Linson]

Using Access Reports and views. I already
managed to show:
1. the income amount for each income account
2. the total income using a group footer sum.
3. the expense amount for each expense account.
4. the total expense using a group footer sum.

I could do those because my tables and views
merged into a view in which the income amounts
and expense amounts and account names are the
same columns whether income or expense.

But now I am stuck on how to show net profit.

Do you not have all the factors of that calculation (seems you do, from the
above)? Can you not calculate the net profit the same way you calculated the
other values?

I wish I knew how to follow a report with another
report that uses another table. But I only know how
to use one table or query in a report. Can a report
use two or more tables (queries)?

Yes, you can include another Report embedded in a Subreport Control.
Sometimes, it can be useful to have all, or almost all, of the actual data
in Reports included in Subreport Controls. However, you might find it
simpler to create a Totals Query and embed that in a Subreport in your
Report Footer.

Or another way I might get the net income would be if I could somehow sum
positive the income and sum-negative the expense.

Without your having described the design of your tables, I have to ask, do
you not have some way to determine whether an item is income or expense.
Many databases carry income as positive numbers and expenses as negative
numbers; if not, they are identifiable so can be summed separately and then
expense subtracted from income.

Good luck with your application.

Larry Linson
Microsoft Access MVP

 

[Guest]

Do you not have all the factors of that calculation (seems you do, from the
above)? Can you not calculate the net profit the same way you calculated the
other values?

I respond to this as part of my response further down.

Yes, you can include another Report embedded in a Subreport Control.
Sometimes, it can be useful to have all, or almost all, of the actual data
in Reports included in Subreport Controls. However, you might find it
simpler to create a Totals Query and embed that in a Subreport in your
Report Footer.

This is very helpful. I think I can use this subreport.

Without your having described the design of your tables, I have to ask, do
you not have some way to determine whether an item is income or expense.
Many databases carry income as positive numbers and expenses as negative
numbers; if not, they are identifiable so can be summed separately and then
expense subtracted from income.

I am getting the data from another database 'Navision', about which I know
little and do not have project time to learn about it. I do have the data
importing to the Access file on which I am working. I also inherited from
some previous programmer this import.

All values are imported as positive values. And the values should show in
the income statement as positive income account values and positive expense
account values. The net income, of course may be positive or negative.

The columns of interest are
[Type], character, which may be '3' or some other characterized
single-integer. If it is '3', then it is income. If it is '5' or '6', then it
is expense.

[Amount], number. It is positive whether it is debit (expense) or credit
(income).

So, it is as you said: There is a way of distinguishing income from expense
amounts.

Good luck with your application.

Larry Linson
Microsoft Access MVP

I thank you that you have already given me the embedded sub-report control
as a way to accomplish what I am trying to do: An Income Statement.