Calculate difference between amounts

[john]

I have a transaction table which partly looks like this:
InOut Amount
In 10
In 10
Out 14
I would like to make a query that calculates the difference between incoming
and outgoing money and would only show that difference, in this case 6.
How can I do this? Is this possible to do in one query?
thanks,
john

[pietlinden@hotmail.com]

Re: Calculate difference between amounts


john wrote:

I have a transaction table which partly looks like this:
InOut Amount
In 10
In 10
Out 14
I would like to make a query that calculates the difference between incoming
and outgoing money and would only show that difference, in this case 6.
How can I do this? Is this possible to do in one query?
thanks,
john

the easy way is to do it with two queries.
(1) qselAmountDirec tion
SELECT xlsInOut.Direct ion, xlsInOut.Amount ,
IIf([Direction]="Out",-1*[Amount],[Amount]) AS AmountDirection
FROM xlsInOut;


(2) qtotAmount
SELECT Sum(qselAmountD irection.Amount Direction) AS SumOfAmountDire ction
FROM qselAmountDirec tion;

or in one query:
SELECT Sum(AmountDirec tion) AS SumOfAmountDire ction
FROM [SELECT xlsInOut.Direct ion, xlsInOut.Amount ,
IIf([Direction]="Out",-1*[Amount],[Amount]) AS AmountDirection
FROM xlsInOut]. AS [%$##@_Alias];

and what's with that fascinating alias? No idea. QBE grid did it.
And when I tried to fix it, the query failed. (Access demons...)

[john]

Re: Calculate difference between amounts

Thanks.
john

<pietlinden@hot mail.comschreef in bericht
news:1158418178 .913908.160880@ b28g2000cwb.goo glegroups.com.. .

>
john wrote:

>I have a transaction table which partly looks like this:
>InOut Amount
>In 10
>In 10
>Out 14
>I would like to make a query that calculates the difference between
>incoming
>and outgoing money and would only show that difference, in this case 6.
>How can I do this? Is this possible to do in one query?
>thanks,
>john

>
the easy way is to do it with two queries.
(1) qselAmountDirec tion
SELECT xlsInOut.Direct ion, xlsInOut.Amount ,
IIf([Direction]="Out",-1*[Amount],[Amount]) AS AmountDirection
FROM xlsInOut;
>
>
(2) qtotAmount
SELECT Sum(qselAmountD irection.Amount Direction) AS SumOfAmountDire ction
FROM qselAmountDirec tion;
>
or in one query:
SELECT Sum(AmountDirec tion) AS SumOfAmountDire ction
FROM [SELECT xlsInOut.Direct ion, xlsInOut.Amount ,
IIf([Direction]="Out",-1*[Amount],[Amount]) AS AmountDirection
FROM xlsInOut]. AS [%$##@_Alias];
>
and what's with that fascinating alias? No idea. QBE grid did it.
And when I tried to fix it, the query failed. (Access demons...)
>

[Bob Quintal]

Re: Calculate difference between amounts

"john" <john@test.comw rote in
news:YpSdnVP2Kq a7npHYRVnyuQ@ca sema.nl:

I have a transaction table which partly looks like this:
InOut Amount
In 10
In 10
Out 14
I would like to make a query that calculates the difference
between incoming and outgoing money and would only show that
difference, in this case 6. How can I do this? Is this
possible to do in one query? thanks,
john
>

use a calculated field to multiply the out rows by -1

SELECT sum(amount*IIF( inout="out",-1,1)) as Balance from
[transaction table];

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Armando]

Re: Calculate difference between amounts

If all you're using the InOut field for is to indicate which way the
(money?) is flowing, why not just do it the normal accounting way? Just
store the Amount, but make it negative if it's flowing Out.

Then just sum them. (similar to the other replies, but the negation has
already been done, and the Amounts themselves are self-describing)

Armando

"john" <john@test.comw rote in message
news:YpSdnVP2Kq a7npHYRVnyuQ@ca sema.nl...

>I have a transaction table which partly looks like this:
InOut Amount
In 10
In 10
Out 14
I would like to make a query that calculates the difference between
incoming and outgoing money and would only show that difference, in this
case 6.
How can I do this? Is this possible to do in one query?
thanks,
john
>

[john]

Re: Calculate difference between amounts

"Armando" <armando@bogus. comschreef in bericht
news:SS2Pg.1306 $Db4.159042@new s1.epix.net...

If all you're using the InOut field for is to indicate which way the
(money?) is flowing, why not just do it the normal accounting way? Just
store the Amount, but make it negative if it's flowing Out.
>
Then just sum them. (similar to the other replies, but the negation has
already been done, and the Amounts themselves are self-describing)

Good idea! Didn't think about that. Have to look if I can easily implement
that in my app without having to change too much in my design.
thanks,
john