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

[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

 

[[MVP] S. Clark]

Please post more info about the data structures involved.

 

[Frank Dubuc]

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 ...

 

[Michel Walsh]

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