Query to group and count distincy products

[bhavin.pardiwala]

Hi have a table as follows in Access

Member ID Product Date Qty Store
1111 pencil 1/1/06 1 Staples
1211 pen 2/5/06 2 Office
Max
1111 Paper 3/1/06 10 Staples
1111 pencil 3/5/06 3 Staples


I would like to write a query which gives me the following

Member ID No of Products
1111 2
1211 1

I would like it to count only unique products even thought they may be
repeated. In the above table, pencil occurs twice but should be treated
as one product.

Can anyone please help?

Thanks

 

[Duane Hookom]

The easiest method might be to create a totals query
SELECT MemberID, Product
FROM [as follows]
GROUP BY MemberID, Product;

Then create another totals query based on the previous query

SELECT MemberID, Count([Product]) as NoOfProducts
FROM qtotPreviousQuery
GROUP BY MemberID;