query for one product only

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?

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


--
Build a little, test a little.


"KrispyData" wrote:

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?

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

"KARL DEWEY" wrote:

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


--
Build a little, test a little.


"KrispyData" wrote:

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?

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.


"KrispyData" wrote:

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

"KARL DEWEY" wrote:

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


--
Build a little, test a little.


"KrispyData" wrote:

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?

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

KrispyData wrote:
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?