query-show data from the 'one' table if criteria is not in 'many'

[Guest]

Using the Northwind Database as an example, I want to run a query to show
customers who have no related record in the orders detail file for a selected
product. The customers do have order detail for other products, but I want
to see who has not ordered a specific product. I'm running Access 2003.
Thanks for your help.

 

[Guest]

Try

SELECT Customers.*
FROM Customers
WHERE (((Customers.CustomerID) Not In (SELECT Orders.CustomerID
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
WHERE [Order Details].ProductID=[Please enter product Id])))

 

[Guest]

I am a beginner and I opened this group up with the exact same question. I
ended up findin the answer in my dummies book, and it worked. (For mine
anyway.)

Try using the query wizard called "Find unmatched query wizard" If you
click on queries and then new you'll see it there and it explains exactly
what you need to do. If that doesn't help, hopefully someone else will give
you an answer, cause like I said, I don't know what I'm doing, I just got
lucky with that one.

 

[Guest]

Thank you, that looks like a good solution, but I guess Northwind wasn't the
best example to use. I don't have enough tables to do the WHERE and Not IN
function. And perhaps I should have mentioned I'm using a form with combo
boxes to select all criteria. My first combo box selects customers with
category, then the next combo box is to select the "productid" - Actually,
I'm looking for customers who have not received certain promotional items.
So instead of having a Orders table and a Order Detail table, I have a
PromoHistory table that contains PromoID, CustomerID and MailingDate. My
combo box shows the PromoID and Promo Description from PromoDescription
table. My query looks like this:

SELECT qryOption1.*, PromoHistory.PromoID
FROM qryOption1 LEFT JOIN PromoHistory ON qryOption1.CustomerID =
PromoHistory.CustomerID
WHERE (((PromoHistory.PromoID) Not Like
Forms!SelectContactsDialog!PromoComboBox

If a H Smith has records for promo1 and promo2 in the PromoHistory table,
when I ask to see customers who have not received promo2, I still get H
Smith, because promo1 meets the criteria. Am I making this harder than it
is?
Thanks

Try

SELECT Customers.*
FROM Customers
WHERE (((Customers.CustomerID) Not In (SELECT Orders.CustomerID
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
WHERE [Order Details].ProductID=[Please enter product Id])))


--
\\// Live Long and Prosper \\//
BS"D

Using the Northwind Database as an example, I want to run a query to show
customers who have no related record in the orders detail file for a selected
product. The customers do have order detail for other products, but I want
to see who has not ordered a specific product. I'm running Access 2003.
Thanks for your help.