Sorting a Query Causes overflow

  • Thread starter Thread starter Guest
  • Start date Start date
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!
 
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...
 
Back
Top