Sorting a Query Causes overflow

G

Guest

I am having a problem with a query that Works fine unsorted, but when I try
to sort it causes an overflow error. I am using Access 2003.

The sql that causes the problem is:
SELECT
qry_MonthlySellThru_Slope_DeltaCalcs.intRetailer,
qry_MonthlySellThru_Slope_DeltaCalcs.strPartNum,
Sum([DeltaProduct])/Sum([TimeDeltaSquared]) AS AnnualSlope
FROM
qry_MonthlySellThru_Slope_DeltaCalcs
GROUP BY
qry_MonthlySellThru_Slope_DeltaCalcs.intRetailer,
qry_MonthlySellThru_Slope_DeltaCalcs.strPartNum
HAVING
(((qry_MonthlySellThru_Slope_DeltaCalcs.intRetailer)=1))
** Removing this line will avoid the error (but I need to sort it!!)
ORDER BY
Sum([DeltaProduct])/Sum([TimeDeltaSquared]);

the SQL for the sub query (qry_MonthlySellThru_Slope_DeltaCalcs) is:

SELECT
qry_MonthlySellThru_Slope_sub.YearPortion,
qry_MonthlySellThru_Slope_sub.intCalYear,
qry_MonthlySellThru_Slope_sub.bytCalMonth,
qry_MonthlySellThru_Slope_sub.intRetailer,
qry_MonthlySellThru_Slope_sub.strPartNum,
([SumOfIntSellThru]-[AvgOfSumOfintSellThru]) AS SellThruDelta,
([YearPortion]-[AvgOfYearPortion]) AS TimeDelta,
[TimeDelta]*[SellThruDelta] AS DeltaProduct,
[TimeDelta]*[TimeDelta] AS TimeDeltaSquared
FROM
qry_MonthlySellThru_Slope_sub INNER JOIN
qry_MonthlySellThru_Slope_RetailerPart_Base
ON
(qry_MonthlySellThru_Slope_sub.intRetailer =
qry_MonthlySellThru_Slope_RetailerPart_Base.intRetailer)
AND
(qry_MonthlySellThru_Slope_sub.strPartNum =
qry_MonthlySellThru_Slope_RetailerPart_Base.strPartNum);

And for each of the other queries is :

qry_MonthlySellThru_Slope_sub
-------------------------------------
SELECT
[intCalYear]+((1/12)*[bytCalMonth]) AS YearPortion,
tmp_MonthlySellThru.bytCalMonth,
tmp_MonthlySellThru.intCalYear,
tmp_MonthlySellThru.intRetailer,
tmp_MonthlySellThru.strPartNum,
Sum(tmp_MonthlySellThru.intSellThru) AS SumOfintSellThru
FROM
tmp_MonthlySellThru
WHERE
(((tmp_MonthlySellThru.strStore) Like "All*"))
GROUP BY
[intCalYear]+((1/12)*[bytCalMonth]),
tmp_MonthlySellThru.bytCalMonth,
tmp_MonthlySellThru.intCalYear,
tmp_MonthlySellThru.intRetailer,
tmp_MonthlySellThru.strPartNum;

qry_MonthlySellThru_Slope_RetailerPart_Base
-----------------------------------------------------

SELECT
qry_MonthlySellThru_Slope_sub.intRetailer,
qry_MonthlySellThru_Slope_sub.strPartNum,
Avg(qry_MonthlySellThru_Slope_sub.SumOfintSellThru) AS
AvgOfSumOfintSellThru,
Avg(qry_MonthlySellThru_Slope_sub.YearPortion) AS AvgOfYearPortion
FROM
qry_MonthlySellThru_Slope_sub
GROUP BY
qry_MonthlySellThru_Slope_sub.intRetailer,
qry_MonthlySellThru_Slope_sub.strPartNum;


All of the calculations / aggregations are performed on an integer field
(intSellThru)in the base table tmp_MonthlySellThru. In case you are curious,
this query calculates the slope of the line of best fit for each part's
Sales figures.

Thanks in advance!
 
G

Guest

Sorry to be one of those annoying people that answers their own questions,
but I just solved my problem....


I changed the line in the first SQL listed below:
Sum([DeltaProduct])/Sum([TimeDeltaSquared]) AS AnnualSlope
to

IIF(Sum([TimeDeltaSquared])=0,0,Sum([DeltaProduct])/Sum([TimeDeltaSquared]))
AS AnnualSlope

Thereby removing any divide by zero errors and now I can sort the query no
problems.

Microsoft if you are watching, please considering improve the quality of
error messages so they are not so useless.

Wesley Baker said:
I am having a problem with a query that Works fine unsorted, but when I try
to sort it causes an overflow error. I am using Access 2003.

The sql that causes the problem is:
SELECT
qry_MonthlySellThru_Slope_DeltaCalcs.intRetailer,
qry_MonthlySellThru_Slope_DeltaCalcs.strPartNum,
Sum([DeltaProduct])/Sum([TimeDeltaSquared]) AS AnnualSlope
FROM
qry_MonthlySellThru_Slope_DeltaCalcs
GROUP BY
qry_MonthlySellThru_Slope_DeltaCalcs.intRetailer,
qry_MonthlySellThru_Slope_DeltaCalcs.strPartNum
HAVING
(((qry_MonthlySellThru_Slope_DeltaCalcs.intRetailer)=1))
** Removing this line will avoid the error (but I need to sort it!!)
ORDER BY
Sum([DeltaProduct])/Sum([TimeDeltaSquared]);

the SQL for the sub query (qry_MonthlySellThru_Slope_DeltaCalcs) is:

SELECT
qry_MonthlySellThru_Slope_sub.YearPortion,
qry_MonthlySellThru_Slope_sub.intCalYear,
qry_MonthlySellThru_Slope_sub.bytCalMonth,
qry_MonthlySellThru_Slope_sub.intRetailer,
qry_MonthlySellThru_Slope_sub.strPartNum,
([SumOfIntSellThru]-[AvgOfSumOfintSellThru]) AS SellThruDelta,
([YearPortion]-[AvgOfYearPortion]) AS TimeDelta,
[TimeDelta]*[SellThruDelta] AS DeltaProduct,
[TimeDelta]*[TimeDelta] AS TimeDeltaSquared
FROM
qry_MonthlySellThru_Slope_sub INNER JOIN
qry_MonthlySellThru_Slope_RetailerPart_Base
ON
(qry_MonthlySellThru_Slope_sub.intRetailer =
qry_MonthlySellThru_Slope_RetailerPart_Base.intRetailer)
AND
(qry_MonthlySellThru_Slope_sub.strPartNum =
qry_MonthlySellThru_Slope_RetailerPart_Base.strPartNum);

And for each of the other queries is :

qry_MonthlySellThru_Slope_sub
-------------------------------------
SELECT
[intCalYear]+((1/12)*[bytCalMonth]) AS YearPortion,
tmp_MonthlySellThru.bytCalMonth,
tmp_MonthlySellThru.intCalYear,
tmp_MonthlySellThru.intRetailer,
tmp_MonthlySellThru.strPartNum,
Sum(tmp_MonthlySellThru.intSellThru) AS SumOfintSellThru
FROM
tmp_MonthlySellThru
WHERE
(((tmp_MonthlySellThru.strStore) Like "All*"))
GROUP BY
[intCalYear]+((1/12)*[bytCalMonth]),
tmp_MonthlySellThru.bytCalMonth,
tmp_MonthlySellThru.intCalYear,
tmp_MonthlySellThru.intRetailer,
tmp_MonthlySellThru.strPartNum;

qry_MonthlySellThru_Slope_RetailerPart_Base
-----------------------------------------------------

SELECT
qry_MonthlySellThru_Slope_sub.intRetailer,
qry_MonthlySellThru_Slope_sub.strPartNum,
Avg(qry_MonthlySellThru_Slope_sub.SumOfintSellThru) AS
AvgOfSumOfintSellThru,
Avg(qry_MonthlySellThru_Slope_sub.YearPortion) AS AvgOfYearPortion
FROM
qry_MonthlySellThru_Slope_sub
GROUP BY
qry_MonthlySellThru_Slope_sub.intRetailer,
qry_MonthlySellThru_Slope_sub.strPartNum;


All of the calculations / aggregations are performed on an integer field
(intSellThru)in the base table tmp_MonthlySellThru. In case you are curious,
this query calculates the slope of the line of best fit for each part's
Sales figures.

Thanks in advance!
Click to expand...
 
Top