#  Other Applications & Softwares  > Access Tables & Databases >  >  MS Access Count duplicate records for each value

## Neyme

Hello,

I am new to Access and need to do a simple task that can be done in Excel - to count duplicate records for each ID that can be simply done in Excel with a pivot table. I need to use Access rather tan Excel because I have millions or the row entries. I really hope one of the Access pros can help me. 

My data in access table looks like below where there are two columns: (1) UniqueID is a primary key and has unique records and (2) the User ID column has duplicate records

UniqueID column  
1
2
3
4
5
6
7
8
9
10

User ID column
345
345
980
234
980
234
345
876
980
980

I need the data look like below and to perform the following actions: (1) to deduplicate User ID column and then (2) to count the number of records in UniqueID column per unique/deduplicated User IDs and insert the count in an additional column.

User ID column
345
980
234
876

Count of UniqueID
3
4
2
1

Thank you in advance!

----------


## vba_php

use the query wizard and select the template query named *find duplicates*.  select the fields that can contain dup info and go through the steps.  then look at the sql access generates and change it accordingly.

----------


## Neyme

Hi vba_php,

Thank you so much for such simple solution! I followed your directions and was able to get what I need.  :Smilie:

----------


## vba_php

good deal.   :Smilie:

----------

