+ Reply to Thread
Results 1 to 5 of 5

MS Access Deduplicate Values and Sum up Numbers for each De-duplicated Value

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

    MS Access Deduplicate Values and Sum up Numbers for each De-duplicated Value

    Hello,

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

    My data in Access table looks like below where there are three columns: (1) UniqueID is a primary key and has unique records and (2) the User ID column that has duplicate records (3) Items Purchased column that have a umber of items that the user purchased.

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

    (2) User ID column
    345
    345
    980
    234
    980
    234
    345
    876
    980
    980

    (3) Items Purchased column
    3
    7
    1
    5
    2
    1
    7
    6
    3
    2


    I need the data look like below and to perform the following actions: (1) to deduplicate User ID column and then (2) to sum up the Items Purchased per unique/deduplicated User ID.

    (1) UniqueID column
    1
    2
    3
    4

    (2) User ID column
    234
    345
    876
    980


    (3) Items Purchased column
    6
    17
    6
    8

    Thank you in advance!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: MS Access Deduplicate Values and Sum up Numbers for each De-duplicated Value

    Assuming your table is called Sheet1, the the following query will generate the results you are seeking
    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

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

    Re: MS Access Deduplicate Values and Sum up Numbers for each De-duplicated Value

    Hi Alan, I am very new to MS Access. Do I understand correctly that I need to enter this query in "Home" tab >"View" > "SQL View"? I would appreciate some guidance. Thank you!

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

    Re: MS Access Deduplicate Values and Sum up Numbers for each De-duplicated Value

    Allan, I got it! I used your sql query and it worked as a charm! Thank you so much!

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: MS Access Deduplicate Values and Sum up Numbers for each De-duplicated Value

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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. generate n random numbers not duplicated for each id numbers with Vba
    By fatoom91 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-21-2019, 03:31 PM
  2. I Need Numbers to Be Duplicated
    By beardmoen in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-20-2016, 11:23 PM
  3. [SOLVED] Deduplicate and merge
    By tiger234 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-11-2012, 01:55 PM
  4. Searching for duplicated numbers
    By ridnickhick in forum Excel General
    Replies: 5
    Last Post: 07-26-2012, 06:39 PM
  5. Using Vlookup for duplicated numbers
    By fodejimi in forum Excel General
    Replies: 5
    Last Post: 05-12-2011, 09:10 AM
  6. [SOLVED] deduplicate data in excel
    By Julie Melbourne in forum Excel General
    Replies: 8
    Last Post: 11-27-2005, 09:50 PM
  7. [SOLVED] Using EXACT to Deduplicate
    By Darrell Halverson via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-02-2005, 03:06 AM

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