 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2008How to view records that do not meet criteria
I have a table with two colums: CustomerID and ProductName The same customer may have just one product or multiple. I can do a query and for the criteria, enter <> "Product X" and the results will show all the customers that do not have Product X in the table. This may also include the same patient that HAS received Product X, it just doesn't show it in the results. What I want to see, though, are customers that have NEVER received Product X and even if they've received another product, I do NOT want that customer listed. Does anyone know how this can be accomplished? Thanks You can restrict the results by means of a subquery. Lets assume you have a table Customers and a table ProductsReceived, the latter being the one you refer to in you post then the following will return the Customers who did not receive Product X SELECT * FROM Customers WHERE NOT EXISTS (SELECT * FROM ProductsReceived WHERE ProductsReceived.CustomerID = Customers.CustomerID AND ProductName = "Product X"); The subquery will return rows where the CustomerID is the same as the outer query's current CustomerID and the product is Product X, so by applying the NOT EXISTS predicate to this only rows from the Customers table where no row exists in the ProductsReceived table for that customer with "Product X" as the ProductName. You can of course join the ProductsReceived table, and Products table, which I assume you also have, to the Customers table in the outer query if you want to return data about which products the customers in question have received. Ken Sheridan Stafford, England > I have a table with two colums: > CustomerID and ProductName [quoted text clipped - 11 lines] > > Thanks So if I want to see which customers did NOT receive product X and I have customer #305 that has received product X and also product Y, with your query below, won't customer #305 still show in the query with product Y because he appears on a different row with a different product? What I would like is for customer #305 to NOT show at all because he has recived product X. > You can restrict the results by means of a subquery. Lets assume you have > a [quoted text clipped - 48 lines] >> >> Thanks No, customer #305 won't be returned if he has ever received product X, regardless of what other products he has received. The query will only return a row from the Customers table if the subquery returns no rows; this is what the NOT EXISTS predicate means. So if customer #305 has received product X his row won't be returned because the subquery will return at least one row and consequently the NOT EXISTS predicate will be False. Whether he's received any other products is irrelevant as the subquery concerns itself only with product X. He may have been stuffed to the gills with a dozen other products, he still won't be returned. Which is what I think you were saying you wanted in you original post. If customer #305 has never received product X on the other hand, the subquery won't return any rows so the NOT EXISTS predicate will be True and he will be returned. Again what other products he may or may not have received are irrelevant. Ken Sheridan Stafford, England > So if I want to see which customers did NOT receive product X and I have > customer #305 that has received product X and also product Y, with your [quoted text clipped - 55 lines] > >> > >> Thanks I see what you're saying now. Thanks for the advice, it seems to work well this way. > No, customer #305 won't be returned if he has ever received product X, > regardless of what other products he has received. The query will only [quoted text clipped - 89 lines] >> >> >> >> Thanks It should do; it’s the standard solution to this sort of problem. Ken Sheridan Stafford, England > I see what you're saying now. > Thanks for the advice, it seems to work well this way. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.