Count the number of times a value appears in a column using Ms Access

[osibindlana]

I would like to count the number of times a value appears in a column using Ms Access

eg.


Name Count
Mike 2
Paul 1
Mike 2
Peter 1

[JoeM]

You can use an Aggregate Query to do so, but note that it then only list each option once (so if the name "Mike" appears twice in your list, the results of the Aggregate Query will list the name "Mike" once with a count of 2 next to it). Not sure if that is acceptable to you.

To do this, simply create a new query, and add the Name field to your query twice. Then click on the Totals (Aggregate) button (it looks like a Sigma). This will add a Totals row under both of your fields in the Query Builder. Leave the first field as "Group By" on the Totals row, but change the second one to "Count". This will give you the count of each name.

If you really want to list out each name multiple times, you can simply then create a second query, that joins your first query with your original data table on the Name field, and then return your Name, Count, and whatever other fields you would like.

[ranman256]

in query design, bring down NAME field twice.
turn on summation icon
change the rightmost NAME , on the TOTAL line, to COUNT

[osibindlana]

Hi Guys, I seem to be missing this

[JoeM]

Hi Guys, I seem to be missing this

Missing what, exactly?

See if this helps clarify what we are telling you to do: https://support.office.com/en-us/art...7-e1024ccfcca8