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.

[vba_php]

good deal.