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