1

I have a dataset similar to the one below in Access:

enter image description here

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?

2
  • According to this help page, basic SQL and queries are better suited to Stack Overflow. This website is primarily for database admin, design, advanced programming.
    – C Perkins
    Sep 27, 2017 at 4:29
  • From a query perspective, you need to COUNT(DISTINCT SITE) (which it looks like you may already realize). I have no idea if Access' Query Designer provides a way to do this, or allows you to modify the query to do this. If you actually see editable text that says COUNT(SITE) or COUNT(*) in the query, try changing it to the above.
    – RDFozz
    Sep 27, 2017 at 16:24

2 Answers 2

1

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.

0

Try this

SELECT  Company ,
        COUNT(Site)
FROM    ( SELECT    Company ,
                    Site
          FROM      Mytable
          GROUP BY  Company ,
                    Site
        ) AS T;
1
  • I think Access doesn't allow count(distinct ..), at least in the older versions that I had been using. Sep 27, 2017 at 9:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.