Can anyone tell why the query below would throw a divide-by-zero
error? The only reason I can see for the error happening would be if
SUM([EXP_NET]) came out to be zero, but this never happens with the
data I'm using. SOME of the values in the EXP_NET column are zero, but
the sum itself is always non-zero.
The query is:
TRANSFORM SUM([EXP_NET]-[RECOVERY])/SUM([EXP_NET])*100 AS DATA SELECT
TBL.EQUIPMENT AS EQUIPMENT,
SUM([EXP_NET]-[RECOVERY])/SUM([EXP_NET])*100 AS TOTAL FROM
REC_DEF_ANALYSIS AS TBL GROUP BY TBL.EQUIPMENT PIVOT TBL.PERIOD;
This works fine until a few zero values show up in the EXP_NET column.
I've verified that the zero values are the culprit by changing them
all to nulls, which the SUM function ignores. The really strange thing
here is that I can run the SUM formulas manually with:
SELECT SUM([EXP_NET]-[RECOVERY])/SUM([EXP_NET])*100 FROM
REC_DEF_ANALYSIS
and I don't get the divide-by zero error.
Anyone have any ideas? Thanks in advance. |