Need help: design a query to extract multiple order information

[82033chn]

Suppose I have a customer_order table where customer's order details
are stored, e.g., Customer I ordered Product A, B, C on day 1, and
Customer II ordered Product B, C, F, G on day 2, and so on.

I could not figure out an "easy" way which a single query can be
designed so I know which customers ordered Product B and C at the same
time, other than doing it awkardly by:

SELECT DISTINCT customer_order.CustomerID, customer_order.Product,
customer_order_1.Product
FROM customer_order INNER JOIN customer_order AS customer_order_1 ON
customer_order.Product = customer_order.Product
WHERE (((customer_order.Product)="B") AND
((customer_order_1.Product)="C"));

What if I need to know the customers who ordered seven products with a
single order? I hate to design a query where the table is linked 7
times! Surely there is a simpler way? Appreciate any help. Thanks.
Please email to (e-mail address removed).

 

[Douglas J Steele]

Are you saying you want to know which customers ordered a specific 7
products?

SELECT customer_order.CustomerID, customer_order.OrderID
FROM customer_order
WHERE customer_order.Product IN ("B", "C", "E", "F", "G", "I", "K")
GROUP BY customer_order.CustomerID, customer_order.OrderID
HAVING Count(*) = 7

Or are you saying that you want to know which customers order exactly 7
products (any 7 products)?

SELECT customer_order.CustomerID, customer_order.OrderID
FROM customer_order
GROUP BY customer_order.CustomerID, customer_order.OrderID
HAVING Count(*) = 7

In the second query, change it to >= 7 if you want those customers who
ordered at least 7 products.

 

[82033chn]

Neither, Doug. Sorry that I did not make myself clear earlier. What I'm
trying to do was finding customers who ordered several specific
products in a single order (the customer may have ordered more products
in that order than I qurried for). For example, Customer I ordered
Product A, B, C, D on day 1, and Customer II ordered Product B, C, F, G
on day 2. If I want to know who ordered B&C regardless of order date,
the answer is Customer I and II. But if I want to know who ordered A,B,
and C, then the answer should just be Customer I. How am I going to do
that? Thanks.

 

[Douglas J Steele]

I don't really see how that's different than the first example I gave.

SELECT customer_order.CustomerID, customer_order.OrderID
FROM customer_order
WHERE customer_order.Product IN ("B", "C")
GROUP BY customer_order.CustomerID, customer_order.OrderID
HAVING Count(*) = 2

would give you Customer I and Customer II

SELECT customer_order.CustomerID, customer_order.OrderID
FROM customer_order
WHERE customer_order.Product IN ("A", "B", "C")
GROUP BY customer_order.CustomerID, customer_order.OrderID
HAVING Count(*) = 3

would give you Customer I.

 

[82033chn]

Hi Doug,

I used your example without grouping the orderID and that's where I
made my mistake. All is clear now and thanks a bunch!

This is also the first time I see the IN ("A", "B") expression and I
figure it is the same as using "A" OR "B" expression. I searched the
Access Help and did not find it. Is it a function and are there other
useful functions like that?

Cheers!

 

[Douglas J. Steele]

IN is part of the SQL language.

You might find the following useful. (Don't worry that they say Access 2000:
they apply to all versions)

http://msdn.microsoft.com/library/en-us/dnacc2k/html/acfundsql.asp
http://msdn.microsoft.com/library/en-us/dnacc2k/html/acintsql.asp
http://msdn.microsoft.com/library/en-us/dnacc2k/html/acadvsql.asp