Using a PivotTable to Count Items in Access

Question

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!

Answer 1

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;

Or create it in the design view as below.