Access Query - Count distinct values I have a dataset similar to the one below in Access:



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 simply counts the Site field but this counts the same site multiple times if it is repeated. My aim is to count each site only once. 

Does anyone have a simple way to achieve this in Access' Query Design?
The key concept here is that you must create more than one query.  Not all results can be obtained by a single query.  It is possible to accomplish this using SQL subqueries (a.k.a. nested queries) so that the overall query can be saved and edited as a single unit, but subqueries are not supported by the Query Designer--they must be edited in SQL View.

Some database systems support aggregate functions which can directly count distinct values in one query.  Access does not support such a function, so you must first write a query which gets distinct values for the Site column.  A query can always refer to another query in the same manner it refers to a table.

Even though the question specifically mentions Design View (Query Designer), it is traditional on Stack Exchange sites to share queries as SQL text.  It is much too tedious to constantly paste screen shots.  Also, SQL text can be copied and pasted directly into SQL view.  At the least you should know/learn how to switch into SQL view so that you can copy and paste there.  For basic SQL statements, just switch back to Design View.

SELECT Company, Site FROM table GROUP BY Company, Site


or

SELECT DISTINCT Company, Site FROM table


The DISTINCT keyword corresponds to the Design View property Unique Values = True.  Save that query as, for example, CompanySites.

Now count values in the saved query:

SELECT Company, Count(Site) As Sites FROM CompanySites




You may want to consider normalizing the database by adding separate [Company] and [Site] tables with appropriate primary and foreign keys.