N
NZSchoolTech
I have a report which has data in 26 columns across the page. There are
7 tables with 3-4 fields in each table that have to be pulled together
to make up the report. In the query, they are all left joined from a
table that contains labels for each row. Thus it is possible for the 7
tables to return null values in different columns of each row where data
has not been input into a field in the record for that row.
I want to calculate averages for the report footer in each of the 26
columns but am having problems with some fields that contain zero
values. I want to remove these zeroes from the average calculation (so
that the rows containing zeroes are not counted as valid data) but can't
see any way of doing this short of using 26 different queries, one for
each column, that exclude records containg zeroes from the original
data. Now, the aggregation functions automatically exclude nulls. I
thought perhaps I could write a custom function to turn zeroes into
nulls but can't see any way of doing this.
Surely there must be a simpler way of filtering the input data so that
the zeroes are treated like nulls and excluded from the count for the
average calculation.
7 tables with 3-4 fields in each table that have to be pulled together
to make up the report. In the query, they are all left joined from a
table that contains labels for each row. Thus it is possible for the 7
tables to return null values in different columns of each row where data
has not been input into a field in the record for that row.
I want to calculate averages for the report footer in each of the 26
columns but am having problems with some fields that contain zero
values. I want to remove these zeroes from the average calculation (so
that the rows containing zeroes are not counted as valid data) but can't
see any way of doing this short of using 26 different queries, one for
each column, that exclude records containg zeroes from the original
data. Now, the aggregation functions automatically exclude nulls. I
thought perhaps I could write a custom function to turn zeroes into
nulls but can't see any way of doing this.
Surely there must be a simpler way of filtering the input data so that
the zeroes are treated like nulls and excluded from the count for the
average calculation.