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:
Make query (View mode):
