Re: SQL too long?

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


.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
Loading