Help needed with ONLY VBA code or query

[John Moore]

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 product1 and/or
product2 and no other product types in the product line.

I cannot get my mind around how to either write a VBA code and/or a
query 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

 

[Brian Wilson]

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 product1 and/or
product2 and no other product types in the product line.

I cannot get my mind around how to either write a VBA code and/or a
query 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

You could do it using subqueries. We don't know table and field names, but
the query should be something like:

SELECT tblCustomer.* FROM tblCustomer WHERE
tblCustomer.CusID IN (SELECT tblSales.CusID FROM tblSales WHERE
tblSales.ProdID IN (5,9))
AND
tblCustomer.CusID NOT IN (SELECT tblSales.CusID FROM tblSales WHERE
tblSales.ProdID NOT IN (5,9))

.... this assumes that 5 and 9 represent the ID's of the 2 products you are
interested in.