Query is Overflowing

[Dan]

Hi,

This query returns records for awhile but then errors with an overflow

SELECT tCS.Customer_ID, tCS.Vendor_ID, (SELECT Sum(Sales) FROM
tbl_Customer_Sales as tCS2 WHERE tCS.Customer_ID = tCS2.Customer_ID AND
tCS.Vendor_ID = tCS2.Vendor_ID AND tCS2.[Year-Month] Like '2005/07') AS
YM_Sales, (SELECT format(Sum(Gross_Margin)/Sum(Sales), "percent") FROM
tbl_Customer_Sales as tCS2 WHERE tCS.Customer_ID = tCS2.Customer_ID AND
tCS.Vendor_ID = tCS2.Vendor_ID AND tCS2.[Year-Month] Like '2005/07') AS
YM_GMP, Sum(Sales) AS LYM_Sales, format(Sum(Gross_Margin)/Sum(Sales),
"percent") AS LYM_GMP, (SELECT Sum(Sales) FROM tbl_Customer_Sales as
tCS2 WHERE tCS.Customer_ID = tCS2.Customer_ID AND tCS.Vendor_ID =
tCS2.Vendor_ID AND (tCS2.[Year-Month] >= '2005/01' AND tCS2.[Year-Month]
<= '2005/07')) AS YTD_Sales, (SELECT
format(Sum(Gross_Margin)/Sum(Sales), "percent") FROM tbl_Customer_Sales
as tCS2 WHERE tCS.Customer_ID = tCS2.Customer_ID AND tCS.Vendor_ID =
tCS2.Vendor_ID AND (tCS2.[Year-Month] >= '2005/01' AND tCS2.[Year-Month]
<= '2005/07')) AS YTD_GMP, (SELECT Sum(Sales) FROM tbl_Customer_Sales as
tCS2 WHERE tCS.Customer_ID = tCS2.Customer_ID AND tCS.Vendor_ID =
tCS2.Vendor_ID AND (tCS2.[Year-Month] >= '2004/01' AND tCS2.[Year-Month]
<= '2004/07')) AS LYTD_Sales, (SELECT
format(Sum(Gross_Margin)/Sum(Sales),"percent") FROM tbl_Customer_Sales
as tCS2 WHERE tCS.Customer_ID = tCS2.Customer_ID AND tCS.Vendor_ID =
tCS2.Vendor_ID AND (tCS2.[Year-Month] >= '2004/01' AND tCS2.[Year-Month]
<= '2004/07')) AS LYTD_GMP
FROM tbl_Customer_Sales AS tCS
WHERE (tCS.[Year-Month] Like '2004/07')
GROUP BY tCS.Customer_ID, tCS.Vendor_ID;

I think this is caused by divide by zero errors finally hitting a
threshold. Is there any way to get around this? I would really like to
return the percent.

TIA,
Dan

 

[Duane Hookom]

You should be trapping all of your divisions like:
IIf(Sum([FieldA]) = 0, 0, Sum([FieldB])/Sum([FieldA]))

--
Duane Hookom
MS Access MVP

Hi,

This query returns records for awhile but then errors with an overflow

SELECT tCS.Customer_ID, tCS.Vendor_ID, (SELECT Sum(Sales) FROM
tbl_Customer_Sales as tCS2 WHERE tCS.Customer_ID = tCS2.Customer_ID AND
tCS.Vendor_ID = tCS2.Vendor_ID AND tCS2.[Year-Month] Like '2005/07') AS
YM_Sales, (SELECT format(Sum(Gross_Margin)/Sum(Sales), "percent") FROM
tbl_Customer_Sales as tCS2 WHERE tCS.Customer_ID = tCS2.Customer_ID AND
tCS.Vendor_ID = tCS2.Vendor_ID AND tCS2.[Year-Month] Like '2005/07') AS
YM_GMP, Sum(Sales) AS LYM_Sales, format(Sum(Gross_Margin)/Sum(Sales),
"percent") AS LYM_GMP, (SELECT Sum(Sales) FROM tbl_Customer_Sales as tCS2
WHERE tCS.Customer_ID = tCS2.Customer_ID AND tCS.Vendor_ID =
tCS2.Vendor_ID AND (tCS2.[Year-Month] >= '2005/01' AND tCS2.[Year-Month]
<= '2005/07')) AS YTD_Sales, (SELECT format(Sum(Gross_Margin)/Sum(Sales),
"percent") FROM tbl_Customer_Sales as tCS2 WHERE tCS.Customer_ID =
tCS2.Customer_ID AND tCS.Vendor_ID = tCS2.Vendor_ID AND (tCS2.[Year-Month]

= '2005/01' AND tCS2.[Year-Month] <= '2005/07')) AS YTD_GMP, (SELECT

Sum(Sales) FROM tbl_Customer_Sales as tCS2 WHERE tCS.Customer_ID =
tCS2.Customer_ID AND tCS.Vendor_ID = tCS2.Vendor_ID AND (tCS2.[Year-Month]

= '2004/01' AND tCS2.[Year-Month] <= '2004/07')) AS LYTD_Sales, (SELECT

format(Sum(Gross_Margin)/Sum(Sales),"percent") FROM tbl_Customer_Sales as
tCS2 WHERE tCS.Customer_ID = tCS2.Customer_ID AND tCS.Vendor_ID =
tCS2.Vendor_ID AND (tCS2.[Year-Month] >= '2004/01' AND tCS2.[Year-Month]
<= '2004/07')) AS LYTD_GMP
FROM tbl_Customer_Sales AS tCS
WHERE (tCS.[Year-Month] Like '2004/07')
GROUP BY tCS.Customer_ID, tCS.Vendor_ID;

I think this is caused by divide by zero errors finally hitting a
threshold. Is there any way to get around this? I would really like to
return the percent.

TIA,
Dan