report between 2 table sets

[Andreas]

I am still stuck with this so, I will explain it in more detail:
I have 4 tables. 2 are for expenses 2 are for income:

Tables:
1) Invoices
2) Invoice Details
===> One-to-many relation

3) Expenses
4) ExpenseDetails
===> One-to-many relation

There is no relationship between Invoices and Expenses tables because they
are irrelevant.
Income from invoices tables can be summarized in a query and the same for
expenses so, I can have 2 queries, one for expenses and 1 for income.

Requirement:
Design a report that will include all expenses and all income, add the total
of expenses and total of income, and then subtract the expenses total from
the income total in order to find profit or loss.

What I have tried so far is to use subform as previously suggested but that
will just show another report that is totally separeted from the other. I
haven't managed to find a way to create a query between the 4 tables as this
would mix expenses with income. Also, I don't see any reason why to make a
relationship between expenses and income as they have no relationship.

What is the proper way to do this?

 

[George Nicholson]

Use a UNION query to combine your 2 queries. Then use the result as the
recordsource of your report.

SELECT "Invoice" as Type, InvoiceID As ID, ItemDescription As Description,
ItemAmount As Amount FROM qryInvoices
UNION SELECT "Expense" as Type, ExpenseID, ExpenseDescription, ExpenseAmount
FROM qryExpenses
Group By Type

UNION queries
1) when using the query designer, you have to use View>SQL & enter the text
"long-hand".
2) use Alias/FieldNames from the 1st Select in its Output
3) require that you have the same # of fields in the tables you are
combining (add Placeholder fields if you have 4 fields you want to show
overall, but one table only has 3 fields: "Null as SomeField")

HTH,

 

[Andreas]

Ok, and in this way I get a unified recordset where the expenses will have
"Expense" type and income will have "Invoice" type. Then, at the report how
can I separate the 2 since they both have the same descriptor/field name?

I can imagine that my calculations can be made based on a condition so I
could sum up only those records with "Income" but will I be able to place
separate them within the report as expenses and as income ? How?

 

[George Nicholson]

In your report, Group on "Type". Include a Footer for that group & add
subtotals on "Amount". That will give you separate totals for Invoices &
Expenses.

In your query, change ExpenseAmount to ExpenseAmount* -1 as ExpenseAmount.
That will show all Income as positive, Expenses as negative & a grand/report
total of the "Amount" field will give you the combined "difference".

Alternatively, on your report you could split "Amount" into 2 side-by-side
columns with the following as ControlSources: =iif([Type] =
"Invoice",[Amount],0) and =iif([Type] = "Expense",[Amount],0)
Wrap the entire expressions above in Sum() to get any Subtotals or Report
totals for the individual columns. Note: this might slow down the report
considerably if you have a lot of data.

HTH,

 

[Andreas]

Is it possible to have different header for expenses and different for
invoicesor should I set the caption name based on condition (IIF)?

In your report, Group on "Type". Include a Footer for that group & add
subtotals on "Amount". That will give you separate totals for Invoices &
Expenses.

In your query, change ExpenseAmount to ExpenseAmount* -1 as ExpenseAmount.
That will show all Income as positive, Expenses as negative & a
grand/report total of the "Amount" field will give you the combined
"difference".

Alternatively, on your report you could split "Amount" into 2 side-by-side
columns with the following as ControlSources: =iif([Type] =
"Invoice",[Amount],0) and =iif([Type] = "Expense",[Amount],0)
Wrap the entire expressions above in Sum() to get any Subtotals or Report
totals for the individual columns. Note: this might slow down the report
considerably if you have a lot of data.

HTH,

Ok, and in this way I get a unified recordset where the expenses will
have "Expense" type and income will have "Invoice" type. Then, at the
report how can I separate the 2 since they both have the same
descriptor/field name?

I can imagine that my calculations can be made based on a condition so I
could sum up only those records with "Income" but will I be able to place
separate them within the report as expenses and as income ? How?