[RBASE-L] - Re: Off Subject - SQL statement help needed

[Jim Limburg]

Larry
Your suggestion did the trick. I appreciate the help on this. I owe you one.
I ended up with a statement being produced like this.
SELECT type AS [T], orderid as [Order],  Order1.custid as [Cust ID], +
t0 AS [First Name], t26 AS [Last Name],  t11 as [Company], +
dateordered as [Ordered], datecompleted as [Completed],  +
salesperson as [Sales Rep], paymentterm as [Terms], +
(SELECT sum(tprice)  FROM [OrderParts] +
WHERE orderid=Order1.orderid) as [Charges], +
(SELECT sum(amount)  FROM [Payments] +
WHERE orderid=Order1.orderid) AS [Payments],  +
([Charges] - [Payments]) as [Balance] +
FROM [Orders] AS Order1  +
INNER JOIN [Customers] ON Order1.custid=Customers.custid  +
WHERE Order1.dateordered BETWEEN #8/22/2004# AND #09/22/2004# AND +
order1.orderID NOT IN (SELECT OrderID FROM tblSchedule) +
AND Order1.dateordered IS NOT NULL AND +
(((SELECT SUM(amount) FROM [Payments] +
WHERE orderid = Order1.orderid)) > 0) ORDER BY Order1.orderid DESC
I did get rid of a few other parts not needed as well, and all looks
good. I do see what you say about it being slow, but I think it will
be acceptable for what is needed. In the case of NULLs there never
should be any for the ORDER ID, so I should be safe.
Albert.. I appreciate you suggestion also, but have to admit that
you talked a bit over the head. I still need to sit down and get a
clean understanding of JOINS on how and where to use them. I have
read some on it, but not put it to practical use so I can keep it
in the ole noggin yet.
Thank you all
Jim Limburg
At 01:26 PM 9/21/2004, you wrote:
Try changing:
> FROM [orders] AS order1 +
> inner JOIN [customers] ON order1.custid=customers.custid +
> WHERE order1.dateordered BETWEEN strglblbegdate  AND strglblenddate +
To:
FROM [orders] AS order1 +
inner JOIN [customers] ON order1.custid=customers.custid +
WHERE +
>> order1.orderID NOT IN (SELECT OrderID FROM tblSchedule) AND +
order1.dateordered BETWEEN strglblbegdate  AND strglblenddate +
(the new code is on the >> line).  You don't need to add tblSchedule to the
FROM clause of main SELECT statement, and do not put it in the middle of the
INNER JOIN syntax.
You should also know the following about MS Access:
1. It is exceedingly slow with the IN statement if a large number of 
values are
returned.

2. It will fail if the IN select returns a NULL as one of its values, so you
should always explicitly protect against that possibility.
--
Larry
--
Larry