Access Count Query with Duplicates

Question

I have an Access query I'm trying to make. I have only 2 fields, an office location and a domain. Some offices only use 1 domain, while some offices use more. However, when I run the query some offices show having over 100 domains, which can't be right since there are only a total of 5 domains. I have not messed with Access since high school, so I'm a bit rusty. Is there any way to make it so that it only shows the number of domains sans duplicates instead of the total number of domains per office?

For instance, the office in Atlanta currently shows having 62 domains, when in reality they use 3. I tried messing with DISTINCT and looked extensively online, but I couldn't find anything.

My current SQL statement is: SELECT [AD Employees].Office, Count([AD Employees].Domain) AS CountOfDomain FROM [AD Employees] GROUP BY [AD Employees].Office;

Patrick Honorez Patrick Honorez · Accepted Answer · 2011-06-27T05:49:08

There is no COUNT DISTINCT in Access, try using a subquery:

SELECT ade.Office, Count(*) as DomainsCount FROM 
(SELECT DISTINCT Office, Domain FROM [AD Employees]) ade
GROUP BY ade.Office

(written quickly, might require fine tuning)
Note that if you save that query and then edit it in SQL view, Access has a strange behaviour, changing parentheses into squera brackets, and forcing you to re-edit the parentheses around the subquery before saving.

Access Count Query with Duplicates

1 Answers