What you need is another sub select to identify the matching records in the third
table, and in the master select, use a LEFT OUTER JOIN, and modify the WHERE clause to
select only NULL records from the third table. This will give you only those results
that do not exist in the third table.
Jim Limburg <[EMAIL PROTECTED]> wrote:
>G-Day all
>
>I have a statement that is working with the columns and such
>that I would like to have. I now need to add another record limiting
>statement. I want to return records in this main clause that don't
>exist in another table by and orderid.
>
>The syntax will look funny to most as it is VB to an access database,
>and no I didn't create it, I just have to work on it.
>
>strTmp1 = "SELECT MID(""RWCS"",type+1,1) 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 #" & strglblBegDate & "# AND #" &
>strglblEndDate & _
>"# AND Order1.datecompleted IS NOT NULL AND " & _
>" (((SELECT SUM(tprice) FROM [OrderParts] WHERE orderid =
>Order1.orderid)-(SELECT SUM(amount) " & _
>" FROM [Payments] WHERE orderid = Order1.orderid)) > 0) " &
>strStoneTypeFromCombo & "ORDER BY Order1.orderid DESC")
>
>Now I want to limit the result above to only records that don't exist in
>another table (tblschedule) by orderid. Here is what I thought would work but
>returns as invalid SQL syntax.
>
>strTmp1 = "SELECT MID(""RWCS"",type+1,1) 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] WHERE orderid NOT
>IN(" & _
>" SELECT Schedule1.orderid FROM Schedule1 WHERE Schedule1.orderid =
>Order1.orderid" & _
>" AS Order1, tblSchedule AS Schedule1 INNER JOIN [Customers] ON
>Order1.custid=Customers.custid " & _
>" WHERE Order1.dateordered IS NOT NULL AND Order1.datecompleted IS NOT NULL
>AND" & _
>" (((SELECT SUM(tprice) FROM [OrderParts] WHERE orderid =
>Order1.orderid)-(SELECT SUM(amount) " & _
>" FROM [Payments] WHERE orderid = Order1.orderid)) > 0)" &
>strStoneTypeFromCombo & "ORDER BY Order1.orderid DESC)"
>
>I added the
>WHERE orderid NOT IN(" & _
>" SELECT Schedule1.orderid FROM Schedule1 WHERE Schedule1.orderid =
>Order1.orderid" & _
>after the From [Orders] section of the clause and the tblSchedule AS Schedule1
>
>The whole purpose is to find orders that have had some payment but have not
>been scheduled.
>
>Jim Limburg
>
>--- RBASE-L
>================================================
>TO POST A MESSAGE TO ALL MEMBERS:
>Send a plain text email to [EMAIL PROTECTED]
>
>(Don't use any of these words as your Subject:
>INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
>REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
>================================================
>TO SEE MESSAGE POSTING GUIDELINES:
>Send a plain text email to [EMAIL PROTECTED]
>In the message SUBJECT, put just one word: INTRO
>================================================
>TO UNSUBSCRIBE:
>Send a plain text email to [EMAIL PROTECTED]
>In the message SUBJECT, put just one word: UNSUBSCRIBE
>================================================
>TO SEARCH ARCHIVES:
>Send a plain text email to [EMAIL PROTECTED]
>In the message SUBJECT, put just one word: SEARCH-n
>(where n is the number of days). In the message body,
>place any
>text to search for.
>================================================
>
>
--
Albert Berry
Management Consultant
RR2 - 1252 Ponderosa Drive
Sparwood, BC � �V0B 2G2
Canada
(250) 425-5806
(250) 425-7259
__________________________________________________________________
Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register
Netscape. Just the Net You Need.
New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp
