subtracting sum of tbl.unit1 from sum of tbl.unit2 in SELECT statement

[bigmike]

I have a database with two tables, Revenue and Expenditure. Each have a budget field. I would like to write a query that will show the sum of each of these two fields and the surplus left after expenditures are subtracted from revenues.

I have tried:

SELECT sum(Revenue.[budget]), sum(Expenditure.[budget]), sum(Revenue.[budget] - sum(Expenditure.[budget])
FROM Revenue, Expenditure;

The above query returns
$13,014,539,791.00, $14,945,685,000.00 and ($1,931,145,209.00)
The only one of these three number that s partially correct is the Expenditures, the middle number, but it's 10x what it should be.
The other two numbers make now sense at all.

Selecting one at a time seems to work unless subtraction is used:
the query SELECT sum(Revenue.[budget]) FROM Revenue;
returns
$16,207,397.00 this is the correct sum
SELECT SUM(Expenditure.[budget]) FROM Expenditure;
returns
$14,945,685.00 this is also the correct sum
and SELECT (SUM(Revenue.[budget] - SUM(Expenditure.[budget])) FROM Revenue, Expenditure;
returns


($1,931,145,209.00) a negative number and not correct even if it was positive

how do I return multiple sums and the difference between the two ?

[pbaldy]

You need a join between the tables on whatever field(s) relate them. If there is none, you need to do them separately.