Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a list of text entries and I want to count how often each entry appears in the list. e.g.


The result would be

Berlin 1
Paris 4
London 2

This result easy do to achieve with an pivot table in MS Excel (see: Count Items in Excel). My data not in spreadsheet in Excel but in a MS Access database table. So in order to avoid constant switching between Access and Excel and I would like to handle everything in Access (either Access 2007 or 2010).

I know there are pivot tables in Access and I know how to display one, but I was unable to find out how to count the number of occurrences.

Thank you!

share|improve this question

Better to do it as a query - that's what databases are good at. Create a new query via Query Design or Query Wizard that has SQL (right click on the tab name and select SQL View) something like:

SELECT Table1.City, Count(Table1.ID) AS CountOfID
FROM Table1
GROUP BY Table1.City;

SQL View

Or create it in the design view as below.

Design View

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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