Count Account Services with multiple occurrences

[crspycrtr]

I need to create a query that counts how many accounts have a specific
service, but there can be many occurences of the same service on an account.

Example
Acct 1234 has limited services for 3 devices.
Acct 1234 has advanced service for 2 devices.
Acct 5678 has limited services for 1 device.
Acct 5678 has advanced service for 3 devices.
Acct 9012 has limited services for 2 devices.
Acct 9012 has advanced service for 0 devices.

What I need to return is that there are:
3 accounts with limited service.
2 accounts with advanced service.

What I am getting is that there are
6 accounts with limited service
5 accounts with advanced service

 

[John Spencer]

You need to create a distinct query for the Account and service type and base
your counts on that query.

Query one:
SELECT DISTINCT Accounts, ServiceType
FROM YourTable

Query Two:
SELECT ServiceType, Count(Accounts) as AccountCount
FROM QueryOne
GROUP BY ServiceType

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County