Help needed with ONLY query or VBA Code

[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 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.

JM

 

[John Welch]

John, let's say the productID for widget1 is 3 and that for widget2 is 5. To
get a list of people who haven't purchased anything but these two things,
you can use:

SELECT DISTINCT Customers.CustomerID, Customers.Firstname,
Customers.Lastname from Customers INNER JOIN Sales on Customers.CustomerID
= Sales.CustomerID WHERE Sales.CustomerID not in (SELECT CustomerID from
Sales where Sales.ProductID <> 3 and Sales.ProductID <>5)

The DISTINCT makes it so it just shows each name once. The subquery (in
parentheses) finds everyone who has purchased anything but widgets 1 or 2.
The main query filsters those people out and therefore shows just people who
have purchased only w1 and w2.

HTH
-John

 

[Chris2]

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.

JM

John Moore,

Forms:

This really depends on where your "inclusion" lists comes from. If
this is going to be changing on a frequent basis, you can make an
extra table, "ProductInclusionList", and use a Form based on
Products to append rows to ProductInclusionList (and delete them out
when you are done). This also has the advantage of "saving" the
last inclusion list you used. If you wanted to, a little expansion
of this idea and you can create and store "inclusion lists"
permanently, so you don't have to keep re-creating them.


Tables:

CREATE TABLE Customers
(CustomerID INTEGER
,FName TEXT(72)
,LName TEXT(72)
,CONSTRAINT pk_Customers
PRIMARY KEY (CustomerID)
)

CREATE TABLE Products
(ProductID INTEGER
,ProductName TEXT(72)
,CONSTRAINT pk_Products
PRIMARY KEY (ProductID)
)

CREATE TABLE Sales
(CustomerID INTEGER
,ProductID INTEGER
,SalesDate DATETIME
,CONSTRAINT pk_Sales
PRIMARY KEY (CustomerID
,ProductID
,SalesDate)
,CONSTRAINT FK_Sales_Customers_CustomerID
FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID)
,CONSTRAINT FK_Sales_Products_ProductID
FOREIGN KEY (ProductID)
REFERENCES Products (ProductID)
)

Note: There should probably by a Quatity column in Sales, but I've
left it out for brevity.


CREATE TABLE ProductInclusionList
(ProductID INTEGER
,CONSTRAINT pk_ProductInclusionList
PRIMARY KEY (ProductID)
,CONSTRAINT FK_ProductInclusionList_Products_ProductID
FOREIGN KEY (ProductID)
REFERENCES Products (ProductID)
)

Sample Data:

Customers:
1, Jay, Smith
2, Dee, Smith
3, Jeff, Smith

Products:
1, Jello
2, Pie
3, Chocolate
4, Turkish Delight
5, Napolean
6, Eclair

Sales:
1, 1, 12/24/2005 12:00:00
1, 2, 12/24/2005 12:00:00
2, 3, 12/23/2005 13:00:00
2, 4, 12/23/2005 13:00:00
2, 6, 12/24/2005 12:00:05
3, 1, 12/24/2005 16:00:00
3, 1, 12/24/2005 18:00:00
3, 2, 12/24/2005 16:00:00
3, 3, 12/24/2005 16:00:00
3, 4, 12/24/2005 16:00:00
3, 5, 12/24/2005 16:00:00
3, 6, 12/24/2005 16:00:00

ProductInclusionList
1
5


Query:

SELECT DISTINCT
C1.LName & ", " & C1.FName
As CustomerName
,P1.ProductName
FROM (Sales AS S1
INNER JOIN
Products AS P1
ON S1.ProductID = P1.ProductID)
INNER JOIN
Customers AS C1
ON S1.CustomerID = C1.CustomerID
WHERE S1.ProductID IN
(SELECT PIL1.ProductID
FROM ProductInclusionList AS PIL1
WHERE PIL1.ProductID = S1.ProductID)

Results:

CustomerName, ProductName
Smith, Jay, Jello
Smith, Jeff, Jello
Smith, Jeff, Napolean


Sincerely,

Chris O.