AA how do you sum only a fields negative values ?

  • Thread starter Thread starter DWL
  • Start date Start date
D

DWL

if a field contains both positive and negative values
how can you total only the field's negative values ?

e.g. 10
-4
20
-6

would give a negative total of -10.
 
Make a Totals query and get the particular field in the grid twice, once
with a Sum total function and once with a Where total function with a <0
criterion.

HTH,
Nikos
 
Hi,



SELECT SUM(f1)
FROM myTable
WHERE f1 <= 0


Hoping it may help,
Vanderghast, Access MVP
 
YOu can't do it in on column. The best way is to create
a query, pull the column with the data you want, then add
two other columsn, one for the expression >0 and one for
<0. Ie., your data columns is SALES, columns one could
be GREATERSALES: SALES > 0 and the other could be
LESSERSALES: SALES < 0.
 
In your query grid
Field: SumNegatives: ([FieldA]<0 * [FieldA])
Total: Sum

This will result in a POSITIVE value so you would need to add "-" if you
want it to be negative
Field: SumNegatives: -([FieldA]<0 * [FieldA])
 
Back
Top