Re: SQL too long?

PMFBI

In addition to the previous sage advice,
you might expand on what "ape-s---" means.

It appears to me that you are doing a lot of
division w/o checking for 0 in denominator.

Since joins are all inner joins, is it possible
that some of those denominator counts could
be zero after joining new table?

A quick test might be to join all the tables
in a new test query and see what counts you
are getting.

Or, bite the bullet and preface all your divisions with
test for 0 in your original query (always a good idea
anyway).

Apologies for butting in (especially with such a WAG),

gary


"OfficeDev18 via AccessMonster.com" wrote:
I have a query with a long SQL statement. It was working all along, but
today
I added tblSOMst01 to it, including the join, and the one field I needed
from
that table. Now all of a sudden Access goes ape-s--- every time I try to
open
the report that's bound to it. I scanned, opened a new db and imported all
the objects, compacted, stood on my head, and I still haven't gotten
resolution. Can anyone make a suggestion? Oh, yes, I put it into MS Word,
and
wordcount tells me the statement is 1,712 characters long including
spaces.
What gives? Thanks in advance. Here's the SQL.

SELECT DISTINCTROW tblDUPSFreight.invno, tblDUPSFreight.ornum, tblSOMst01.
OrigAmt AS SOAmt, tblDUPSFreight.PkgQty, tblDUPSFreight.BillAmt AS
CurrentCharge, qryTotalFrtSub.SumOfBillAmt AS TotalCharge, tblDUPSFreight.
Incentive, tblDUPSFreight.BatchNo, tblDUPSFreight.Adjustment,
tblDUPSFreight.
Reason, tblARMst01.fob, Sum([extprice])/Count(tblARTrn01.invno) AS eprice,
((
([BillAmt]=Sum([extprice])/Count([tblARTrn01].[invno])) Or (([tblARMst01].
[fob]="PREPAID") And (Sum([extprice])/Count([tblARTrn01].[invno])=0) And (
[BillAmt]>0)) Or (([tblARMst01].[fob]="ADD FRT") And (Abs(Sum([extprice])
/Count([tblARTrn01].[invno])-([BillAmt]+[Incentive]))<1))) And (Not
[adjustment]) Or ([tblARMst01].[fob] Like "NEXT DAY*")) AS NoProb
FROM (((tblDUPSFreight INNER JOIN tblARTrn01 ON tblDUPSFreight.invno =
tblARTrn01.invno) INNER JOIN tblARMst01 ON tblDUPSFreight.invno =
tblARMst01.
invno) INNER JOIN qryTotalFrtSub ON tblDUPSFreight.invno = qryTotalFrtSub.
invno) INNER JOIN tblSOMst01 ON tblDUPSFreight.ornum = tblSOMst01.sono
WHERE (((tblARTrn01.item) Like "SHI*"))
GROUP BY tblDUPSFreight.invno, tblDUPSFreight.ornum, tblSOMst01.OrigAmt,
tblDUPSFreight.PkgQty, tblDUPSFreight.BillAmt,
qryTotalFrtSub.SumOfBillAmt,
tblDUPSFreight.Incentive, tblDUPSFreight.BatchNo,
tblDUPSFreight.Adjustment,
tblDUPSFreight.Reason, tblARMst01.fob
HAVING (((((([BillAmt]=Sum([extprice])/Count([tblARTrn01].[invno])) Or ((
[tblARMst01].[fob]="PREPAID") And
(Sum([extprice])/Count([tblARTrn01].[invno])
=0) And ([BillAmt]>0)) Or (([tblARMst01].[fob]="ADD FRT") And (Abs(Sum(
[extprice])/Count([tblARTrn01].[invno])-([BillAmt]+[Incentive]))<1))) And
(Not [adjustment]) Or ([tblARMst01].[fob] Like "NEXT DAY*")))=False))
ORDER BY tblDUPSFreight.invno;

--
Sam

Message posted via http://www.accessmonster.com


.

Loading