Query to group and count distincy products

  • Thread starter Thread starter bhavin.pardiwala
  • Start date Start date
B

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
 
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;
 
Back
Top