Access Count Distinct with Grouping

[kimosavi]

Hi. I know this question has been around for quite some time but I don't seem to find the correct syntax for my query.

I have a table with 3 fields: Bucket, Vendors, Docs.

I want to run a query that tells me how many unique vendors and docs are in each Bucket

Bucket Vendor# Doc#
CAT1 10 50
CAT2 12 30


let me know if someone can give me a hand please....

Thanks!

 

[jal]

Try this:

SELECT Bucket, Count([Vendor#]) as VendorCount , Count([Doc#]) as DocCount FROM table1
GROUP BY Bucket

 

[jal]

It occured to me today that I didn't think this through. Maybe this would work:

SELECT Bucket, Count([Vendor#]) as VendorCount FROM table1
GROUP BY Bucket, [Vendor#]

I think you would need a second query for docs:

SELECT Bucket, Count([Doc#]) as DocCount FROM table1
GROUP BY Bucket, [Doc#]