division by Zero and expression to complex

D

DawnTreader

Hello All

here is the SQL i am working on:

SELECT
dbo_CUSTOMER.NAME AS CustomerName,
dbo_CUSTOMER_ORDER.CUSTOMER_ID AS CustomerID,
dbo_CUSTOMER_ORDER.ID AS OrderID,
dbo_CUST_ORDER_LINE.PART_ID AS IMWPartID,
dbo_PART.DESCRIPTION AS Description,
dbo_CUST_ORDER_LINE.ORDER_QTY AS Qty,

nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0)
AS VisualCostPrice,

nz([dbo_PART].[UNIT_PRICE],0) AS VisualListPrice,
(nz([dbo_PART].[UNIT_PRICE],0)*0.9) AS VLPLess10Percent,

([VisualListPrice]-[VisualCostPrice])/[VisualListPrice] AS Margin,

((nz([dbo_PART].[UNIT_PRICE],0)*0.9)-(nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0)))/(nz([dbo_PART].[UNIT_PRICE],1)*0.9)
AS VCPmVLPL10P

FROM (dbo_CUSTOMER_ORDER LEFT JOIN (dbo_CUST_ORDER_LINE LEFT JOIN dbo_PART
ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID) ON dbo_CUSTOMER_ORDER.ID =
dbo_CUST_ORDER_LINE.CUST_ORDER_ID) LEFT JOIN dbo_CUSTOMER ON
dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID

GROUP BY dbo_CUSTOMER.NAME, dbo_CUSTOMER_ORDER.CUSTOMER_ID,
dbo_CUSTOMER_ORDER.ID, dbo_CUST_ORDER_LINE.PART_ID, dbo_PART.DESCRIPTION,
dbo_CUST_ORDER_LINE.ORDER_QTY,
nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0),
nz([dbo_PART].[UNIT_PRICE],0), (nz([dbo_PART].[UNIT_PRICE],0)*0.9),
([VisualListPrice]-[VisualCostPrice])/[VisualListPrice],
((nz([dbo_PART].[UNIT_PRICE],0)*0.9)-(nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0)))/(nz([dbo_PART].[UNIT_PRICE],1)*0.9)

HAVING (((dbo_CUSTOMER.NAME) Like "imw*shang*") AND
((dbo_CUST_ORDER_LINE.PART_ID) Is Not Null) AND
((dbo_CUST_ORDER_LINE.ORDER_QTY)>0))

ORDER BY ([VisualListPrice]-[VisualCostPrice])/[VisualListPrice] DESC;

i know the spaces are unnecessary, but i thought it would help in readability.

the problem is:

((nz([dbo_PART].[UNIT_PRICE],0)*0.9)-(nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0)))/(nz([dbo_PART].[UNIT_PRICE],1)*0.9)
AS VCPmVLPL10P

this is the same as the margin calculation. the problem is that if i use the
margin calculation i get 2 parameter dialogs, the problem with the
VCPmVLPL10P is that at one point i got a division by zero error and now i am
getting an expression to complex.

i dont understand why i get either. the formula actually worked a few
minutes before.

i am thinking that it may have something to do with the fact that the data
comes from a live database that is being constantly used. but i need to be
sure that the SQL is correct, hence my post.

any and all help appreciated.
 
J

John Spencer

The problem is probably in your ORDER BY clause.

You need to do the entire calculation over in the ORDER BY Clause
Also, you don't need to Group By since you are not aggregating any
values. I think that the following will work.

SELECT DISTINCT dbo_CUSTOMER.NAME AS CustomerName,
dbo_CUSTOMER_ORDER.CUSTOMER_ID AS CustomerID,
dbo_CUSTOMER_ORDER.ID AS OrderID,
dbo_CUST_ORDER_LINE.PART_ID AS IMWPartID,
dbo_PART.DESCRIPTION AS Description,
dbo_CUST_ORDER_LINE.ORDER_QTY AS Qty,

nz([dbo_PART]![UNIT_MATERIAL_COST],0)+
nz([dbo_PART]![UNIT_LABOR_COST],0)+
nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz
([dbo_PART]![UNIT_SERVICE_COST],0)
AS VisualCostPrice,

nz([dbo_PART].[UNIT_PRICE],0) AS VisualListPrice,
(nz([dbo_PART].[UNIT_PRICE],0)*0.9) AS VLPLess10Percent,

([VisualListPrice]-[VisualCostPrice])/[VisualListPrice] AS Margin,

((nz([dbo_PART].[UNIT_PRICE],0)*0.9)
-
(nz([dbo_PART]![UNIT_MATERIAL_COST],0)
+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)
+nz([dbo_PART]![UNIT_SERVICE_COST],0)))
/(nz([dbo_PART].[UNIT_PRICE],1)*0.9)
AS VCPmVLPL10P

FROM (dbo_CUSTOMER_ORDER LEFT JOIN
(dbo_CUST_ORDER_LINE LEFT JOIN dbo_PART
ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID)
ON dbo_CUSTOMER_ORDER.ID = dbo_CUST_ORDER_LINE.CUST_ORDER_ID)
LEFT JOIN dbo_CUSTOMER
ON dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID


WHERE (((dbo_CUSTOMER.NAME) Like "imw*shang*") AND
((dbo_CUST_ORDER_LINE.PART_ID) Is Not Null) AND
((dbo_CUST_ORDER_LINE.ORDER_QTY)>0))

ORDER BY (nz([dbo_PART].[UNIT_PRICE],0)

- nz([dbo_PART]![UNIT_MATERIAL_COST],0)+
nz([dbo_PART]![UNIT_LABOR_COST],0)+
nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz
([dbo_PART]![UNIT_SERVICE_COST],0) )

/ nz([dbo_PART].[UNIT_PRICE],0)) DESC


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello All

here is the SQL i am working on:

SELECT
dbo_CUSTOMER.NAME AS CustomerName,
dbo_CUSTOMER_ORDER.CUSTOMER_ID AS CustomerID,
dbo_CUSTOMER_ORDER.ID AS OrderID,
dbo_CUST_ORDER_LINE.PART_ID AS IMWPartID,
dbo_PART.DESCRIPTION AS Description,
dbo_CUST_ORDER_LINE.ORDER_QTY AS Qty,

nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0)
AS VisualCostPrice,

nz([dbo_PART].[UNIT_PRICE],0) AS VisualListPrice,
(nz([dbo_PART].[UNIT_PRICE],0)*0.9) AS VLPLess10Percent,

([VisualListPrice]-[VisualCostPrice])/[VisualListPrice] AS Margin,

((nz([dbo_PART].[UNIT_PRICE],0)*0.9)-(nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0)))/(nz([dbo_PART].[UNIT_PRICE],1)*0.9)
AS VCPmVLPL10P

FROM (dbo_CUSTOMER_ORDER LEFT JOIN (dbo_CUST_ORDER_LINE LEFT JOIN dbo_PART
ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID) ON dbo_CUSTOMER_ORDER.ID =
dbo_CUST_ORDER_LINE.CUST_ORDER_ID) LEFT JOIN dbo_CUSTOMER ON
dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID

GROUP BY dbo_CUSTOMER.NAME, dbo_CUSTOMER_ORDER.CUSTOMER_ID,
dbo_CUSTOMER_ORDER.ID, dbo_CUST_ORDER_LINE.PART_ID, dbo_PART.DESCRIPTION,
dbo_CUST_ORDER_LINE.ORDER_QTY,
nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0),
nz([dbo_PART].[UNIT_PRICE],0), (nz([dbo_PART].[UNIT_PRICE],0)*0.9),
([VisualListPrice]-[VisualCostPrice])/[VisualListPrice],
((nz([dbo_PART].[UNIT_PRICE],0)*0.9)-(nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0)))/(nz([dbo_PART].[UNIT_PRICE],1)*0.9)

HAVING (((dbo_CUSTOMER.NAME) Like "imw*shang*") AND
((dbo_CUST_ORDER_LINE.PART_ID) Is Not Null) AND
((dbo_CUST_ORDER_LINE.ORDER_QTY)>0))

ORDER BY ([VisualListPrice]-[VisualCostPrice])/[VisualListPrice] DESC;

i know the spaces are unnecessary, but i thought it would help in readability.

the problem is:

((nz([dbo_PART].[UNIT_PRICE],0)*0.9)-(nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0)))/(nz([dbo_PART].[UNIT_PRICE],1)*0.9)
AS VCPmVLPL10P

this is the same as the margin calculation. the problem is that if i use the
margin calculation i get 2 parameter dialogs, the problem with the
VCPmVLPL10P is that at one point i got a division by zero error and now i am
getting an expression to complex.

i dont understand why i get either. the formula actually worked a few
minutes before.

i am thinking that it may have something to do with the fact that the data
comes from a live database that is being constantly used. but i need to be
sure that the SQL is correct, hence my post.

any and all help appreciated.
Click to expand...
 
D

DawnTreader

Hello John

the select distinct conflicted with the order by clause. when i took the
distinct out i still get the division by zero error.

if i take out the complex formula:

(((nz([dbo_PART].[UNIT_PRICE],1)*0.9)-(nz([dbo_PART]![UNIT_MATERIAL_COST],1)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0))))/(nz([dbo_PART].[UNIT_PRICE],1)*0.9)
AS VCPmVLPL10P

and just do this:

([VisualListPrice]-[VisualCostPrice])/[VisualListPrice] AS Margin

the results should be the same. the only problem is because i am calculating
on fields that dont have immeadiate data it gives me "parameter" dialogs for
the 2 fields and then runs the query. as well in this scenario i cant sort on
the margin column, it gives me the error division by zero.

current SQL:

SELECT dbo_CUSTOMER.NAME AS CustomerName, dbo_CUSTOMER_ORDER.CUSTOMER_ID AS
CustomerID, dbo_CUSTOMER_ORDER.ID AS OrderID, dbo_CUST_ORDER_LINE.PART_ID AS
IMWPartID, dbo_PART.DESCRIPTION AS Description, dbo_CUST_ORDER_LINE.ORDER_QTY
AS Qty,
nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0)
AS VisualCostPrice, nz([dbo_PART].[UNIT_PRICE],0) AS VisualListPrice,
(nz([dbo_PART].[UNIT_PRICE],0)*0.9) AS VLPLess10Percent,
(([VisualListPrice]-[VisualCostPrice])/[VisualListPrice]) AS Margin
FROM (dbo_CUSTOMER_ORDER LEFT JOIN (dbo_CUST_ORDER_LINE LEFT JOIN dbo_PART
ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID) ON dbo_CUSTOMER_ORDER.ID =
dbo_CUST_ORDER_LINE.CUST_ORDER_ID) LEFT JOIN dbo_CUSTOMER ON
dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID
WHERE (((dbo_CUSTOMER.NAME) Like "imw*shang*") AND
((dbo_CUST_ORDER_LINE.PART_ID) Is Not Null) AND
((dbo_CUST_ORDER_LINE.ORDER_QTY)>0))
GROUP BY dbo_CUSTOMER.NAME, dbo_CUSTOMER_ORDER.CUSTOMER_ID,
dbo_CUSTOMER_ORDER.ID, dbo_CUST_ORDER_LINE.PART_ID, dbo_PART.DESCRIPTION,
dbo_CUST_ORDER_LINE.ORDER_QTY,
nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0),
nz([dbo_PART].[UNIT_PRICE],0), (nz([dbo_PART].[UNIT_PRICE],0)*0.9),
(([VisualListPrice]-[VisualCostPrice])/[VisualListPrice])
ORDER BY (([VisualListPrice]-[VisualCostPrice])/[VisualListPrice]) DESC;


John Spencer said:
The problem is probably in your ORDER BY clause.

You need to do the entire calculation over in the ORDER BY Clause
Also, you don't need to Group By since you are not aggregating any
values. I think that the following will work.

SELECT DISTINCT dbo_CUSTOMER.NAME AS CustomerName,
dbo_CUSTOMER_ORDER.CUSTOMER_ID AS CustomerID,
dbo_CUSTOMER_ORDER.ID AS OrderID,
dbo_CUST_ORDER_LINE.PART_ID AS IMWPartID,
dbo_PART.DESCRIPTION AS Description,
dbo_CUST_ORDER_LINE.ORDER_QTY AS Qty,

nz([dbo_PART]![UNIT_MATERIAL_COST],0)+
nz([dbo_PART]![UNIT_LABOR_COST],0)+
nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz
([dbo_PART]![UNIT_SERVICE_COST],0)
AS VisualCostPrice,

nz([dbo_PART].[UNIT_PRICE],0) AS VisualListPrice,
(nz([dbo_PART].[UNIT_PRICE],0)*0.9) AS VLPLess10Percent,

([VisualListPrice]-[VisualCostPrice])/[VisualListPrice] AS Margin,

((nz([dbo_PART].[UNIT_PRICE],0)*0.9)
-
(nz([dbo_PART]![UNIT_MATERIAL_COST],0)
+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)
+nz([dbo_PART]![UNIT_SERVICE_COST],0)))
/(nz([dbo_PART].[UNIT_PRICE],1)*0.9)
AS VCPmVLPL10P

FROM (dbo_CUSTOMER_ORDER LEFT JOIN
(dbo_CUST_ORDER_LINE LEFT JOIN dbo_PART
ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID)
ON dbo_CUSTOMER_ORDER.ID = dbo_CUST_ORDER_LINE.CUST_ORDER_ID)
LEFT JOIN dbo_CUSTOMER
ON dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID


WHERE (((dbo_CUSTOMER.NAME) Like "imw*shang*") AND
((dbo_CUST_ORDER_LINE.PART_ID) Is Not Null) AND
((dbo_CUST_ORDER_LINE.ORDER_QTY)>0))

ORDER BY (nz([dbo_PART].[UNIT_PRICE],0)

- nz([dbo_PART]![UNIT_MATERIAL_COST],0)+
nz([dbo_PART]![UNIT_LABOR_COST],0)+
nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz
([dbo_PART]![UNIT_SERVICE_COST],0) )

/ nz([dbo_PART].[UNIT_PRICE],0)) DESC


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello All

here is the SQL i am working on:

SELECT
dbo_CUSTOMER.NAME AS CustomerName,
dbo_CUSTOMER_ORDER.CUSTOMER_ID AS CustomerID,
dbo_CUSTOMER_ORDER.ID AS OrderID,
dbo_CUST_ORDER_LINE.PART_ID AS IMWPartID,
dbo_PART.DESCRIPTION AS Description,
dbo_CUST_ORDER_LINE.ORDER_QTY AS Qty,

nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0)
AS VisualCostPrice,

nz([dbo_PART].[UNIT_PRICE],0) AS VisualListPrice,
(nz([dbo_PART].[UNIT_PRICE],0)*0.9) AS VLPLess10Percent,

([VisualListPrice]-[VisualCostPrice])/[VisualListPrice] AS Margin,

((nz([dbo_PART].[UNIT_PRICE],0)*0.9)-(nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0)))/(nz([dbo_PART].[UNIT_PRICE],1)*0.9)
AS VCPmVLPL10P

FROM (dbo_CUSTOMER_ORDER LEFT JOIN (dbo_CUST_ORDER_LINE LEFT JOIN dbo_PART
ON dbo_CUST_ORDER_LINE.PART_ID = dbo_PART.ID) ON dbo_CUSTOMER_ORDER.ID =
dbo_CUST_ORDER_LINE.CUST_ORDER_ID) LEFT JOIN dbo_CUSTOMER ON
dbo_CUSTOMER_ORDER.CUSTOMER_ID = dbo_CUSTOMER.ID

GROUP BY dbo_CUSTOMER.NAME, dbo_CUSTOMER_ORDER.CUSTOMER_ID,
dbo_CUSTOMER_ORDER.ID, dbo_CUST_ORDER_LINE.PART_ID, dbo_PART.DESCRIPTION,
dbo_CUST_ORDER_LINE.ORDER_QTY,
nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0),
nz([dbo_PART].[UNIT_PRICE],0), (nz([dbo_PART].[UNIT_PRICE],0)*0.9),
([VisualListPrice]-[VisualCostPrice])/[VisualListPrice],
((nz([dbo_PART].[UNIT_PRICE],0)*0.9)-(nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0)))/(nz([dbo_PART].[UNIT_PRICE],1)*0.9)

HAVING (((dbo_CUSTOMER.NAME) Like "imw*shang*") AND
((dbo_CUST_ORDER_LINE.PART_ID) Is Not Null) AND
((dbo_CUST_ORDER_LINE.ORDER_QTY)>0))

ORDER BY ([VisualListPrice]-[VisualCostPrice])/[VisualListPrice] DESC;

i know the spaces are unnecessary, but i thought it would help in readability.

the problem is:

((nz([dbo_PART].[UNIT_PRICE],0)*0.9)-(nz([dbo_PART]![UNIT_MATERIAL_COST],0)+nz([dbo_PART]![UNIT_LABOR_COST],0)+nz([dbo_PART]![UNIT_BURDEN_COST],0)+nz([dbo_PART]![UNIT_SERVICE_COST],0)))/(nz([dbo_PART].[UNIT_PRICE],1)*0.9)
AS VCPmVLPL10P

this is the same as the margin calculation. the problem is that if i use the
margin calculation i get 2 parameter dialogs, the problem with the
VCPmVLPL10P is that at one point i got a division by zero error and now i am
getting an expression to complex.

i dont understand why i get either. the formula actually worked a few
minutes before.

i am thinking that it may have something to do with the fact that the data
comes from a live database that is being constantly used. but i need to be
sure that the SQL is correct, hence my post.

any and all help appreciated.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Requerying Automatically 1
Read Only Query, need a little help 1
Queries painfully slow, some general pointers please? 9
Expression Exceeds 1024 character limit of design view, HELP!? 1

Top