query for one product only

  • Thread starter Thread starter KrispyData
  • Start date Start date
K

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?
 
Try this --
SELECT [customer]
FROM [sales]
WHERE [product] = "belts" AND [product] Not In("socks", "gloves", "scarves");
 
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 said:
Try this --
SELECT [customer]
FROM [sales]
WHERE [product] = "belts" AND [product] Not In("socks", "gloves", "scarves");


--
Build a little, test a little.


KrispyData said:
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?
Click to expand...
Click to expand...
 
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 said:
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 said:
Try this --
SELECT [customer]
FROM [sales]
WHERE [product] = "belts" AND [product] Not In("socks", "gloves", "scarves");


--
Build a little, test a little.


KrispyData said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top