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!


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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