Re: SQL too long?

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 <vjohns@xxxxxxxxxxxxxxxxxx>
Please feel free to quote anything I say here.


OfficeDev18 via AccessMonster.com wrote:

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

Bob Barrows [MVP] wrote:

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" 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
.