calculate how many records with at least of 3 fields higher than 0

  • Thread starter Thread starter Frank Dubuc
  • Start date Start date
F

Frank Dubuc

Hi,

I made a query that do the sum of 12 different fields.
Each of these fields are regroup by 3 (4 groups). I want
to put this in a report but I need another data.

I need for each of these groups, the number of records
where for the 3 corresponding fields at least one is higher
than zero.

I know how to do that seperately but I don't how to put all
these data in the same query and after in the same report.

Frank
 
All the fields are numeric. Each record for each field are
summed in another field of the query.

I want to get to sum of each record that has at least one
field out of 3 that is over zero.

I have though of a solution but it's not elegant. If I
could use more than one query in my report I would be able
to show what I want in my report:

First group:
number of records with at least one over zero: __
number of A : __
number of B : __
number of C : __

Second group:
number of records with at least one over zero: __
number of A : __
number of B : __
number of C : __

And so on ...
 
Hi,


If your solution is not elegant, it is probably because your data is not
normalized. Without normalization, we can't do a lot, but your sum should be
like:

Nz(a, 0) + Nz(b, 0) + Nz(c, 0)


and your criteria:

WHERE ABS( Nz(a>0, 0) + Nz(b>0, 0) + Nz(c>0, 0) ) >= 1


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top