Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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.

Berlin
Paris
London
London
Paris
Paris
Paris

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .