subtracting two fields from unrelated tables (1 Viewer)

[T-Dal]

Hey,
i have 4 tables in total three of which have a reltion with one another:

1.Client
2.Company
3.Function

from these tables i have a query which has one field which conveys the total income

in the remaining table called 'epenses' i have made a query which conveys the total outcome

what i want to do is create a report which subtracts the outcome away from the income, however when i use the report wizard it says that the wizard cant connect these two fields.

is it possible to subtract 2 fields from 2 unrelated tables?

 

[Kiwiman]

Howzit

Try a union query. This requires each query in the union to have the same number of fields, and ensure that the fields are similar in nature. The first query will be your revenue, and the second your expenses. Use the union query as the recordsource of your report.

Something along the lines of:

Select field1,field2,field3 from tableA
union
select field1,field2,field3 from tableb

 

[T-Dal]

Thanks kiwiman, when i done this i have both values in one query, however they are in one field and 2 records, instead i i want 2 fields and one record, how can this be done?

EDIT: oops its ok i've used a macro to set a text box to the net value in a form

 

[Pat Hartman]

The only way to join two tables or queries is by a common field. Since you are summarizing away all details, you can add a field in the query with a value of 1. That will give you a common field with appropriate values on which to join.