help doing subtraction in a query

[Anja]

Hello everyone,

I need a query where I calculate the net profit (or loss) in the given
transactions. So, I have to sum up all the positive value transactions
and subtract (actually add since the sum here will be negative) the
sum of all negative value transactions.

So, I have a table called Records_T which holds a field called Amount.

I can calculate the sum of all +ve records as follows:

SELECT Sum(Records_T.Amount) AS AMOUNT FROM Records_T WHERE
(Records_T.Amount)>0

Similarly, the negative records sum is:

SELECT Sum(Records_T.Amount) AS AMOUNT FROM Records_T WHERE
(Records_T.Amount)<0

Now, how can I combine these queries so that I get the sum of the
values returned by these two queries??

Thanks for any help you can give me. Really appreciate it.

Anja

 

[Guest]

Two ways to do it -- first just sum without criteria.
SELECT Sum(Records_T.Amount) AS AMOUNT FROM Records_T
Second combine the queries in a third query.
SELECT [FirstQuery].[AMOUNT] + [SecondQuery].[AMOUNT] AS [Total Amount]
FROM [FirstQuery], [SecondQuery];

OR you can union the two queries and then use that query as source for a
totals query.

 

[Guest]

Seems like a homework question in that a lot of folks have the exact same
table, filed, and requirement.

 

[GARCIASREMODELING]

Hello everyone,

I need a query where I calculate the net profit (or loss) in the given
transactions. So, I have to sum up all the positive value transactions
and subtract (actually add since the sum here will be negative) the
sum of all negative value transactions.

So, I have a table called Records_T which holds a field called Amount.

I can calculate the sum of all +ve records as follows:

SELECT Sum(Records_T.Amount) AS AMOUNT FROM Records_T WHERE
(Records_T.Amount)>0

Similarly, the negative records sum is:

SELECT Sum(Records_T.Amount) AS AMOUNT FROM Records_T WHERE
(Records_T.Amount)<0

Now, how can I combine these queries so that I get the sum of the
values returned by these two queries??

Thanks for any help you can give me. Really appreciate it.

Anja