D
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
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