+ Reply to Thread
Results 1 to 4 of 4

MS Access Count duplicate records for each value

  1. #1
    Registered User
    Join Date
    11-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    94

    MS Access Count duplicate records for each value

    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!
    Last edited by Neyme; 11-18-2020 at 04:54 PM.

  2. #2
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,626

    Re: MS Access Count duplicate records for each value

    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.
    -Adam (please don't call me ""sir"" or ""vba_php"")!

  3. #3
    Registered User
    Join Date
    11-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    94

    Thumbs up Re: MS Access Count duplicate records for each value

    Hi vba_php,

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

  4. #4
    Valued Forum Contributor vba_php's Avatar
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    1,626

    Re: MS Access Count duplicate records for each value

    good deal.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count number of duplicate records.
    By Vivek2705 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-01-2018, 05:42 AM
  2. Count only number of values within duplicate records
    By davidharper2005 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-14-2015, 03:40 AM
  3. Count single records when duplicate records exist
    By omeng414 in forum Excel General
    Replies: 4
    Last Post: 11-27-2014, 03:42 PM
  4. Count duplicate records
    By stats09 in forum Excel General
    Replies: 9
    Last Post: 03-23-2010, 03:26 AM
  5. Merging records / creating new columns based on duplicate records
    By duklaprague in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-19-2007, 10:32 AM
  6. Replies: 0
    Last Post: 07-19-2007, 02:58 AM
  7. count duplicate records in a group
    By Mparekh in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-21-2005, 10:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1