View Single Post
  #4 (permalink)  
Old December 22nd, 2010, 03:29 PM
Old Pedant Old Pedant is offline
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Read the *FULL* sub-query:
Code:
    SELECT DISTINCT O.VendorID
    FROM Customers AS C INNER JOIN Orders AS O
    ON ( O.customerID=C.customerID AND C.State='NY' )
The vendorID is *NOT* coming from the Customers table...it's coming from the Orders table. Perhaps it would have been less confusing if I had written that at
Code:
    SELECT DISTINCT O.VendorID
    FROM Orders AS O INNER JOIN Customers AS C
    ON ( O.customerID=C.customerID AND C.State='NY' )
but the order the tables appear is not important for an INNER join.

****************

Access almost surely can't handle that query, as is, because it would be too complex for its tiny brain.

But what you can do is take that inner query (the one posted just above in *this* message) and STORE it in Access. That is, bring up Access, create a new query in design mode, SQL view. Then save the query under an easy to remember name. Say you save it as "NYVendors".

Then you simply USE that query AS IF IT IS A TABLE in the main query, thus:
Code:
SELECT vendorName 
FROM Vendors  
WHERE vendorID NOT IN (  SELECT VendorID FROM NYVendors )
ORDER BY vendorName
Reply With Quote
The Following User Says Thank You to Old Pedant For This Useful Post:
03jh01 (December 22nd, 2010)