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.

Create a query via the database and insert the required table

Select the Records_T.Amount field into the query grid.

On the tool bar, click on the totals symbol (the sigma or Greek S) and check that the totals row appears in the query grid.


Select sum from the drop down list for the field "Amount" in this row.

Run the query and it will add up both the negative and positive values to give an overall sum

You can of course filter this for values greater than or less than 0 via the criteria.


Hope this helps






Michael109

Unless you need to show the positives and the negatives separately, why even separate the two to then later add? Just simply add them ALL to start with. The negative ones will subtract out of the positive ones anyway.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division