Help needed with ONLY query or VBA Code

[Tony Ciconte]

I have a table of customers who may have purchased numerous types of
products. Finding out who purchased what is easy. However, what I need
to determine is which customer has purchased ONLY one or two
particular products and NOTHING else.

There is a customers table where each customer has a unique ID, a
product table where each product has a unique ID, and a sales table
that has records for each customer and the products they purchased. I
would like to find out which customers purchased only widget1 and/or
widget2 and no other widget types in the product line.

I cannot get my mind around how to either write a query and/or VBA
code to determine which customer may have purchased only one product
regardless of how many of the one item they purchased.

Any and all help is greatly appreciated.

TC

 

[Guest]

Hi Tony,

What an interesting problem!

SELECT *
FROM Customers INNER JOIN Sales
ON Customers.CustID = Sales.CustID_FK
WHERE Sales.ProdID In (1,2)
AND Customers.CustID NOT IN
(SELECT Sales.CustID_FK
FROM Sales
WHERE Sales.ProdID Not In (1,2));

The subquery pulls out all the customers who bought products except for the
two in question. Then that subquery is used to eliminate those customers in
the main query.