Access help, SQL/queries & forms.

[Wollington]

I'm trying to develop a cashbook in access to store inward transactions and expenditure transactions. I then want to be able to get the balance. Money in and money out are stored in 2 different tables they both have the amount column.
So for this I need to be able to do the sum of tblIn.Amount - the sum of tblOut.Amount.

I want both totals and the balance to be shown on a form.

So firstly i tried making a form which had a text box with =Sum([tblIn].[Amount]), the same for out and then a sum to take Out from In on the form.
However access took it upon itself to multiply the sum of tblIn.Amount by however many records there were in that table.(and the same with out)

So then I created 2 queries to perform the Sums
SELECT Sum(Amount) AS TotalIn
FROM tblIn;
(and the same for out)
This worked until I tried to use it from a form, I just got NAME? in the text box instead of the value. And when I tried to add both of them to a form it wouldn't let me.

So How can I perform this seemingly simple task?


------------------
Microsoft has acknowledged that its engineers substituted certain file names with the phrase, "Netscape engineers are weenies," in some of its internet software.

Oh well now we all know why Nutscrape is so crap!
============
< System Specs >

[Wollington]

Got it to work!
I have the 2 queries which are then brought together in a third query which the form will accept.

If there's a better way to do it i'm all ears.

------------------
Microsoft has acknowledged that its engineers substituted certain file names with the phrase, "Netscape engineers are weenies," in some of its internet software.

Oh well now we all know why Nutscrape is so crap!
============
< System Specs >