Using a PivotTable to Count Items in Access
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.
Related videos on Youtube
Sandra
Updated on September 18, 2022Comments
-
Sandra over 1 year
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
ParisThe result would be
Berlin 1
Paris 4
London 2This 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!