Simple Access counting queries

[damon.blackmore]

Hi, hoping someone can help a relative newbie with a pretty simple
query. My database (Access 2007) has three tables:

Customers
Products
Purchases (many-to-one links to both of the other tables, this is
basically a linking table)

I have two simple queries I'd like to get out of this database, but
I'm a bit stuck on how to construct the SQL. Any direction you can
give me would be helpful.

1. List of all customers who have purchased 2 or more products (or 3
or more products, or 4+, etc.)

2. List of all customers who have purchased both Product A and Product
B (or A, B, and C, or B and C, etc).

 

[Guest]

First one. You'll need to put in the proper field names.

SELECT Customers.CustID,
Count(Purchases. ProdID) AS NumberPurchases
FROM Customers INNER JOIN Purchases
ON Customers.CustID = Purchases.CustID
GROUP BY Customers.CustID
HAVING Count(Purchases. ProdID >1 ;

 

[Guest]

Forget the my first attempt. Found some typos:

SELECT Customers.CustID, Customers.Names,
Count(Purchases.ProdID) AS NumberPurchases
FROM Customers INNER JOIN Purchases
ON Customers.CustID = Purchases.CustID
GROUP BY Customers.CustID
HAVING Count(Purchases.ProdID >1) ;

 

[Guest]

The second one. Not so simple at all. Not sure that it will work:

SELECT Customers.CustID, Customer.Names,
Count(P1.ProdID) AS NumberPurchases
FROM Customers INNER JOIN
(SELECT DISTINCT Purchases.CustID, Purchases.ProdID
FROM Purchases) as P1
ON Customers.CustID = P1.CustID
GROUP BY Customers.CustID
HAVING Count(P1.ProdID >1) ;