Query to count number of unique entries in a field for each value in another field

[John_jones]

Hi all
First post so please be gentle. I'm new to access and trying to do what I assumed was a simple query but have hit a roadblock and not found any other solutions online. I have a table setup in access with the following format:




I am wanting to create a query which generates a table summarising how many sites each company is at. I.e. it will return something like this:

Alpha - 2
Bravo - 2
Charlie - 2
Delta - 2

I have tried creating a query which is simply Counts the Site field but this counts the same site multiple times if it is repeated (I.e. Alpha returns a value of 4). My aim is to count each site only once.

Does anyone have a simple way to achieve this in Access' Query Design?

[June7]

Requires two queries.

First query:

SELECT Company, Site FROM table GROUP BY Company, Site; OR SELECT DISTINCT Company, Site FROM table;

Second query:

SELECT Company, Count(Site) AS CountSite FROM Query1 GROUP BY Company;

All in one SQL:

SELECT Company, Count(Site) AS CountSite FROM (SELECT DISTINCT Company, Site FROM table) AS Query1 GROUP BY Company;

[John_jones]

THanks June but I can't seem to get your SQL query to work.

My table name is 'Licence'
Company field name is actually 'Organisation_Name'
Site is actually 'Site_ID'

I have entered this:
SELECT Licence.Organisation_Name, Count(Licence.Site_ID) AS CountSite_ID
FROM (SELECT DISTINCT Licence.Organisation_Name, Licence.Site_ID FROM table) AS [Query1], Licence
GROUP BY Licence.Organisation_Name;


I get a Syntax Error in the FROM clause but it looks like it should be fine to me.

[June7]

Remove Licence from the outer query because you are not aggregating the table, you are aggregating the inner query. Use your table name in the inner query.

SELECT Organisation_Name, Count(Site_ID) AS CountSite_ID
FROM (SELECT DISTINCT Organisation_Name, Site_ID FROM Licence) AS [Query1]
GROUP BY Organisation_Name;