and/or query

[hasan]

i need help writing an and/or expression in a select
query. i have a table of product sales for a fiscal year.
every product corresponsds to a category from "A"
to "D". i would like identify my "full service"
customers. in order to be "full service", customers must
have purchased products from 3 distinct categories, one of
which, must be from category "A" and the other two may be
from categories "B" or "C" or "D" or "E". please help.

 

[Tom Ellison]

Dear Hasan:

The logic for this is moderately difficult.

I recommend starting with a query that lists customers and the
categories from which they have purchased. This may be just those two
columns. Make it DISTINCT so you don't list any category twice for
the same customer.

SELECT DISTINCT customer, category FROM SomeTable

Use your actual table and column names in the above. Save this query
using a name of your choice. I am refering to this query as ThatQuery
below. Test to see it works.

Now, do you have, or will you ever have, any categories other than A -
E? If not, then you don't need logic for the customer having two
categories from B - E. You would only need to say that the customer
just have a selection from A and a total of 3 categories. I'm
assuming A - E are all the categories you'll ever have, or that, if
you have other categories, they will be candidates for the 3 total
distinct categories.

(By the way, do you mean EXACTLY 3 categories or a minimum of 3
categories? I'm writing this for a minimum of 3 categories.)

From the 2 column DISTINCT query above, do this:

SELECT customer
FROM ThatQuery Q
WHERE exists (SELECT * FROM ThatQuery Q1
WHERE Q1.customer = Q.customer
AND Q1.category = "A")
GROUP BY Customer
HAVING COUNT(*) >= 3

For exactly 3 categories, change the last line from >= to =.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Vinson]

i need help writing an and/or expression in a select
query. i have a table of product sales for a fiscal year.
every product corresponsds to a category from "A"
to "D". i would like identify my "full service"
customers. in order to be "full service", customers must
have purchased products from 3 distinct categories, one of
which, must be from category "A" and the other two may be
from categories "B" or "C" or "D" or "E". please help.

I'd suggest creating a Totals query:

SELECT CustomerID, Count(*) FROM Sales
WHERE Category IN ("B", "C", "D", "E")
GROUP BY CustomerID
HAVING Count(*) > 1;

Save this as qryTwoOrMore;

then create a new query

SELECT <customer information>
FROM Customers INNER JOIN qryTwoOrMore
ON Customers.CustomerID = qryTwoOrMore.CustomerID
WHERE EXISTS(SELECT CustomerID FROM Sales WHERE Sales.Category = "A"
AND Sales.CustomerID = Customers.CustomerID);