SQL too long?

[OfficeDev18 via AccessMonster.com]

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;

 

[Douglas J. Steele]

Try aliasing the tables with single character aliases so that you don't need
as much table name in the statement:

SELECT DISTINCTROW A.invno, A.ornum, E.OrigAmt AS SOAmt, A.PkgQty, A.BillAmt
AS
CurrentCharge, D.SumOfBillAmt AS TotalCharge, A.Incentive, A.BatchNo,
A.Adjustment, A.Reason, C.fob, Sum([extprice])/Count(B.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 AS A INNER JOIN tblARTrn01 AS B ON A.invno =
B.invno) INNER JOIN tblARMst01 AS C ON A.invno = C.invno) INNER JOIN
qryTotalFrtSub AS D ON A.invno = D.invno) INNER JOIN tblSOMst01 AS E ON
A.ornum = E.sono
WHERE (((B.item) Like "SHI*"))
GROUP BY A.invno, A.ornum, E.OrigAmt, A.PkgQty, A.BillAmt, D.SumOfBillAmt,
A.Incentive, A.BatchNo, A.Adjustment,A.Reason, C.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 A.invno;


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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;

 

[John Spencer]

If that doesn't work (aliasing), then save the SQL as a named query and use the
saved query (by name) as the report's record source.

Try aliasing the tables with single character aliases so that you don't need
as much table name in the statement:

SELECT DISTINCTROW A.invno, A.ornum, E.OrigAmt AS SOAmt, A.PkgQty, A.BillAmt
AS
CurrentCharge, D.SumOfBillAmt AS TotalCharge, A.Incentive, A.BatchNo,
A.Adjustment, A.Reason, C.fob, Sum([extprice])/Count(B.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 AS A INNER JOIN tblARTrn01 AS B ON A.invno =
B.invno) INNER JOIN tblARMst01 AS C ON A.invno = C.invno) INNER JOIN
qryTotalFrtSub AS D ON A.invno = D.invno) INNER JOIN tblSOMst01 AS E ON
A.ornum = E.sono
WHERE (((B.item) Like "SHI*"))
GROUP BY A.invno, A.ornum, E.OrigAmt, A.PkgQty, A.BillAmt, D.SumOfBillAmt,
A.Incentive, A.BatchNo, A.Adjustment,A.Reason, C.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 A.invno;

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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;

 

[Gary Walter]

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

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;

 

[OfficeDev18 via AccessMonster.com]

Good morning, guys,

and thanks for replying. While I haven't tried aliasing yet, I just want to
give you a snapshot of the status. The query/report worked several times this
morning, and in fact took an additional minor change without crashing. Now,
however, as I'm in the process of modifying the sister query (see my comment
to John below), Access keeps crashing again.

By the way, the denominators can't be 0, Gary, although good thought, because
if a denominator was 0 there would be no data at all for that invno, not even
a blank line. The lowest possible denominator is 1.

As to what 'ape-s---' means, I should have been more explicit. I got the
"Sorry for the inconvenience but Access has got to close..." screen. About 15
iterations of message, reopening the db, opening the query, message, ad
amnauseum. Even after I created a new object and imported all the objects and
worked on the NEW db I still got the same message. By the way, Gary, what's
PMFBI and WAG?

Also, John, because of the complexity of the query, I originally made it a
named query so as not to load down the report. In fact I have two parallel
queries, and depending on the OpenArgs argument of the OpenReport statement,
I assign either of these two queries as the report's record source.

By the way, Doug, I have a question about aliasing. Can I do aliasing in
Access's query design interface? That is, will Access save the query with the
aliases, or will it rename them as the original table names as part of the
'Save' operation? Also, will the saving of about 200 bytes appreciably alter
the stability of the query?

Thanks again,

Sam

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

I have a query with a long SQL statement. It was working all along, but
today

[quoted text clipped - 41 lines]

(Not [adjustment]) Or ([tblARMst01].[fob] Like "NEXT DAY*")))=False))
ORDER BY tblDUPSFreight.invno;

 

[Bob Barrows [MVP]]

Good morning, guys,

By the way, Gary, what's PMFBI and WAG?

Pardon Me for Butting In
Wild-As Guess

By the way, Doug, I have a question about aliasing. Can I do aliasing
in Access's query design interface?

Yes. Right-click the table and choose Properties (unles the Properties
window is already open). You should see a field for Alias in the Properties
window.

Bob Barrows

 

[OfficeDev18 via AccessMonster.com]

Ha-Ha. Thanks, Bob.

Well I aliased, using Bob's instruction (learn something new every day!), and
Access still bombs with the same message. I did this (aliased) with the
sister query I mentioned, which I designed slightly differently, and MS Word
tells me the wordcount is 1,308 including spaces. What now?

Sam

 

[Vincent Johns]

I haven't done a thorough analysis here, but I suspect (this is an
onageristic guess on my part) that the number of characters needed to
express the SQL may not be as important as the number of aggregate
functions like Sum() and Count() that you invoke along the way. Have
you considered partitioning your Query so that some of this work can be
done in stages? Although what you now have may be correct, assuming no
simian behavior on the part of Access, splitting up the Query may also
make it easier to debug if you later encounter any questions concerning
its correctness, or if you need to revise it in some way. For example,
you said that you added [tblSOMst01] to the Query, so apparently your
requirements had changed in some way.

Concerning questions of limitations in your version of Access, you might
want to check the Help topic "Microsoft Access specifications" and
follow the link labeled "Read about Access database query
specifications". For example, in my copy of Access 2000, it says that
the "Number of characters in an SQL statement" has a maximum value of
"approximately 64,000", so you probably have some wiggle room there,
even without using abbreviated aliases for some of the names. But I do
think that aliases, judiciously used, can make the SQL easier to read,
and IMHO making your design easy to understand is at least as important
as avoiding software crashes. Otherwise, how can you determine if the
results are of any value to you? A crash is probably better for your
than wrong results that look valid.

BTW, in case you were wondering, an onager (Equus hemionus; see
http://en.wikipedia.org/wiki/Onager) is a large mammal belonging to the
horse family and native to the deserts of Syria, Iran, India, and Tibet.
It is sometimes known as the Half Ass or the Asian Wild Ass.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Ha-Ha. Thanks, Bob.

Well I aliased, using Bob's instruction (learn something new every day!), and
Access still bombs with the same message. I did this (aliased) with the
sister query I mentioned, which I designed slightly differently, and MS Word
tells me the wordcount is 1,308 including spaces. What now?

Sam

Pardon Me for Butting In
Wild-As Guess



Yes. Right-click the table and choose Properties (unles the Properties
window is already open). You should see a field for Alias in the Properties
window.

Bob Barrows

[...]

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;

 

[Gary Walter]

Hi Sam,

I think it is safe to say that the sql
you have provided does not come
close to exceeding "length limit."

That "limit" does include length of
any queries in the FROM clause though
(whose sql you have not provided).

But, I doubt that is what is happening here.

Your noproblem/HAVING bothers me.
(I could be wrong)

Since the HAVING happens *after* all
the records have been "assembled"
I might suggest a strategy of using
a middle query that provides the
test conditions, then a final query
that AND's and OR's the conditions
to provide final records.

It's a lot easier to set up aliases
in Query Designer so I may have
typed something wrong, but would
not something like the following
give you all the "values" you need
to find "noprobs" in a followup query?

(I don't know where [extprice] comes
from, but would have liked to replaced
them with something like "D.extprice")

SELECT DISTINCTROW
D.invno,
D.ornum,
SOM.OrigAmt AS SOAmt,
D.PkgQty,
D.BillAmt AS CurrentCharge,
Q.SumOfBillAmt AS TotalCharge,
D.Incentive,
D.BatchNo,
D.Adjustment,
D.Reason,
ARM.fob,
Sum([extprice])/Count(ART.invno) AS eprice,
(Sum([extprice])/Count(ART.invno))
- (D.BillAmt + D.Incentive) AS ebal
FROM
(((tblDUPSFreight As D
INNER JOIN
tblARTrn01 As ART
ON D.invno = ART.invno)
INNER JOIN
tblARMst01 As ARM
ON D.invno = ARM.invno)
INNER JOIN
qryTotalFrtSub As Q
ON D.invno = Q.invno)
INNER JOIN
tblSOMst01 As SOM
ON D.ornum = SOM.sono
WHERE
(ART.item Like "SHI*")
GROUP BY
D.invno,
D.ornum,
SOM.OrigAmt,
D.PkgQty,
D.BillAmt,
Q.SumOfBillAmt,
D.Incentive,
D.BatchNo,
D.Adjustment,
D.Reason,
ARM.fob
ORDER BY D.invno;

Again, I could be wrong
and you are welcome to ignore.

good luck,

gary

 

[Gary Walter]

Hi Sam,

One other thing....

do you really need DISTINCTROW
with your grouping query?

If all the fields are in the GROUP BY
that make a returned record unique,
then DISTINCTROW would be redundant
I would think.

You know your data best...

I don't believe that's your problem though...

good luck,

gary

 

[OfficeDev18 via AccessMonster.com]

Good morning,

and thanks to everybody who wrote. I just l-o-v-e learning new things -
especially as to what an onager is - a fancy name for a wild ass!

Anyhow, to update, I decided to attack the HAVING clause, especially when it
was pretty convoluted. Also, in some way I had the impression you all were
not too enthused with it either. To make a long story shorter, I restructured
it, and put it into bite-sized clauses. I wouldn't be surprised, though, if
ultimately what did it in was incorrect parenthesizing (I thought it was
right.....). I did the changes in the sister query, because the first one
started working spontaneously, as I told you on Friday. The sister q is now
working. In case you're interested what the restructured Having clause looks
like, here it is:

NoProb: ((.[fob]="PREPAID") Or (.[fob]="PP0") Or ((.[fob]="PP1500")
And ([E].[OrigAmt]>1500)) And ([soe]=0)) Or ((.[fob]="ADD FRT") Or ((.
[fob]="PP1500") And ([E].[OrigAmt]<1500)) And ([soe]>0)) Or ((.[fob]
="UPSCOL") And ([BillAmt]>0)) Or ((.[fob]="FRT COLLECT") And ([soe]=0) And
([BillAmt]>0)) Or ((.[fob]="3RD PARTY") And ([BillAmt]>0))

Oh, yes, two things. 1 - I aliased, as you can see, and 2 - I managed to
change the HAVING clause into a GROUP BY / WHERE combo (the WHERE clause
contains the requirement " = False"). I don't know how/why it did that, but I
won't argue with a working q either.

Thanks again,

Sam