Results 1 to 2 of 2
  1. #1
    LukeSmith is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Posts
    1

    Sort Duplicate Values In 2 Fields To Create 1 Record

    Hi there,



    I have a massive spreadsheet of over 900,000 rows x 2 columns

    There are duplicate values in both columns but I would like to sort these out so they group together somehow, for example:

    Chocolate Steve
    Chocolate Clive
    Chocolate Scott
    Sandwich Frank
    Chicken Steve
    Chicken Clive
    Beef Julian
    Beef Scott
    Beef Pete
    Beef Steve
    Cake Francis
    Cake Johnny
    Cracker Gary
    Cracker Johnny
    Cracker Richard

    And what I would ideally like to do is combine all of the data, so that I can produce the following result:

    Chocolate SteveCliveScott
    Sandwich Frank
    Chicken SteveClive
    Beef JulianScottPeteSteve
    Cake FrancisJohnny
    Cracker GaryJohnnyRichard

    Now, being new to Microsoft Access (I usually use excel for formulas like this, but this spreadsheet is far too large for excel), I'm not 100% sure what my best options to achieve this,

    any advice would be greatly appreciated,

    All the best

    Luke

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    The only way I can think of at the moment is to create a "Totals" query that returns just the data from the first column:
    Code:
    SELECT Table1.Field1FROM Table1
    GROUP BY Table1.Field1;
    In the attached example db I have called this "qryGroupBy".
    Create a function in a general module. I have called mine "fnGetNames".
    Create a query based on the query "qryGroupBy" that uses "fnGetNames" to return the names. I have called this query "qryGetNames".

    Using a user defined function in a query can be slow when the number of records is large but until some one comes by with something quicker, this does at least produce the right result. Hope this helps.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-23-2014, 06:30 PM
  2. Unable to create duplicate record on subform
    By nika.duncan in forum Programming
    Replies: 9
    Last Post: 12-02-2013, 02:26 PM
  3. Replies: 20
    Last Post: 09-12-2012, 06:52 PM
  4. Replies: 6
    Last Post: 02-21-2012, 03:34 PM
  5. Prevent Duplicate Values on Combination of Two Fields
    By LornaM in forum Database Design
    Replies: 8
    Last Post: 05-05-2009, 11:16 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums