Division by zero error when executing query

290 views Asked by At

I am using a Crosstab query to calculate the percentage from a pair of numbers and a Make query to put the result into a different table.

Source data is something like this:

ITEMS    |PAIRS    |VAL
Element1 |dividend |43
Element1 |divisor  |33
Element2 |dividend |65
Element3 |dividend |73
Element4 |dividend |32
Element4 |divisor  |23
Element3 |divisor  |49

PLEASE NOTE: In a real situation, it is entirely possible that one of the two elements is missing (the divisor part of Element2, in this case).

Here is how I made the Crosstab query (named QCross) to overcome the 'Overflow' error.

TRANSFORM Sum(TRawData.Val) AS SumOfVAL
SELECT TRawData.ITEMS, IIf(IsError(Sum(IIf(TRawData.[PAIRS]='dividend', TRawData.[VAL],0))/Sum(IIf(TRawData.[PAIRS]='divisor',TRawData.[VAL],0))),0,Sum(IIf(TRawData.[PAIRS]='dividend',TRawData.[VAL],0))/Sum(IIf(TRawData.[PAIRS]='divisor',TRawData.[VAL],0))) AS MYRES
FROM TRawData
GROUP BY TRawData.ITEMS
PIVOT TRawData.PAIRS;

And this is the Make query

SELECT QCross.ITEMS, QCross.MYRES INTO TOutcome
FROM QCross;

The Crosstab query is working well, however I am facing with a 'division by zero' error when the Make query is executed (!Run); it worth to note the error doesn't comes up in 'View' mode.

Where am I doing wrong?

Crosstab query execution:

enter image description here

Make query (View mode):

enter image description here

0

There are 0 answers