query for one product only

[KrispyData]

I have a Customer Orders table that includes the following fields as an
example:

customer
product (socks, gloves, scarves, belts)
sales

I want to create a query for all customers that order belts exclusively.
i'm having a difficult time figureing out to create such a query. If i use
"belts" as a query criteria, it will give me every customer that orders
belts, but not customers that ONLY order belts. Can anyone offer any
suggestions?

 

[KARL DEWEY]

Try this --
SELECT [customer]
FROM [sales]
WHERE [product] = "belts" AND [product] Not In("socks", "gloves", "scarves");

 

[KrispyData]

Thanks, Karl. What if the list of other products is more than just 4 items -
more like 100 items! Is there another way?

Try this --
SELECT [customer]
FROM [sales]
WHERE [product] = "belts" AND [product] Not In("socks", "gloves", "scarves");


--
Build a little, test a little.

I have a Customer Orders table that includes the following fields as an
example:

customer
product (socks, gloves, scarves, belts)
sales

I want to create a query for all customers that order belts exclusively.
i'm having a difficult time figureing out to create such a query. If i use
"belts" as a query criteria, it will give me every customer that orders
belts, but not customers that ONLY order belts. Can anyone offer any
suggestions?

 

[KARL DEWEY]

Two queries as I do not subqueries. First pull list of all who did not order
belts.
qryNoBelts
SELECT [customer]
FROM [sales]
WHERE [product] <> "belts";

SELECT [customer]
FROM [sales] LEFT JOIN [qryNoBelts] ON [sales].[customer] =
[qryNoBelts].[customer]
WHERE [customer] Is Null;


--
Build a little, test a little.

Thanks, Karl. What if the list of other products is more than just 4 items -
more like 100 items! Is there another way?

Try this --
SELECT [customer]
FROM [sales]
WHERE [product] = "belts" AND [product] Not In("socks", "gloves", "scarves");


--
Build a little, test a little.

I have a Customer Orders table that includes the following fields as an
example:

customer
product (socks, gloves, scarves, belts)
sales

I want to create a query for all customers that order belts exclusively.
i'm having a difficult time figureing out to create such a query. If i use
"belts" as a query criteria, it will give me every customer that orders
belts, but not customers that ONLY order belts. Can anyone offer any
suggestions?

 

[John Spencer]

Try a query that looks like the following

SELECT *
FROM [CustomerOrders]
WHERE [Customer] NOT IN
(SELECT [Customer]
FROM [CustomerOrders]
WHERE [Product] <> "Belt")

In query design view
== Add your table
== Select the fields you want to see
== Enter the following as criteria under Customer
NOT IN (SELECT [Customer] FROM [CustomerOrders] WHERE [Product] <> "Belt")
(replace the table and field names with your table and field names.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County