Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing duplicates from tables (2000)

    I have a table which contains multiple values, l would like to know
    how l could go about producing a query to remove these duplicate values from the table.

    I use Access 200 and therfore do not have the remove duplicates query.

    Any help would be appreciated.

    Justin.

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing duplicates from tables (2000)

    Access 200 is a pretty ancient version <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    If you go to the query menu, and select Create query by using wizard, then click on New, that should bring up a window with the option of creating a find duplicates query. Otherwise, example SQL below:

    SELECT First(Table.Field) AS [Field Name], Count(Table.Field) AS NumberOfDups
    FROM Table
    GROUP BY Table.Field
    HAVING (((Count(Table.Field))>1));
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing duplicates from tables (2000)

    Do you want to permanently delete all but one of each duplicate? If so, I found the following step by step approach very helpful.
    On a copy of the database:
    Copy the table structure only (Copy and Paste)
    set an index (no duplicates) on the field that you used to determine duplicates. If you used multiple fields, you will need to set a multi-field index.
    Create an append query that will append all the records from the original into the copy.
    You will get an error saying xx records can't be appended due to - the query will append one of the duplicate records and drop the others.
    After the append runs, open up the new table and see if all the records you want are there.
    If so, rename the original (just in case) and then name the copy with the original name.

    Worked like a charm for me.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Removing duplicates from tables (2000)

    Justin

    Thi sis another way to get unique values from a query, after you do this you can convert it into a make table query.
    Jerry

  5. #5
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing duplicates from tables (2000)

    I tried that but it did not appear to rid of any of the duplicates.

    I think the only solution is to use the give query above, do you have any oher suggestions?

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing duplicates from tables (2000)

    In your query's SQL, after the word SELECT, try adding DISTINCT, so it looks like this:

    SELECT DISTINCT Table.Field FROM Table;

    etc...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Removing duplicates from tables (2000)

    My method is good if you are creating look up tables especially when creating them from existing tables or queries. Obviously if you have a lot of fields there is a very good chance that there will not be unique values in a field.

    NYIntensity's suggestion is a solid method for finding duplicates and is the tried and tested way. Just to clarify...are you trying to find the duplicates where you are comparing against one field or a number of fields. I am just checking as you have a number of threads going at the moment and I can't help but feel they are all interconnected. Is Extracting correct data from text file (2000) <post:=591,726>post 591,726</post:> the reason that you are looking for duplicates from 6 different files? If it is then could you not create a union query of the 6 files you are uploading and then run a Duplicates query against the union?
    Jerry

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Removing duplicates from tables (2000)

    ...and then after all that here is a MSKB 209183 to talk you through the removal of duplicates in Access 2000...enjoy, nobody said it was going to be easy <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Jerry

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing duplicates from tables (2000)

    Do MSKB articles make *anything* easy? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  10. #10
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing duplicates from tables (2000)

    That

  11. #11
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing duplicates from tables (2000)

    This query worked but it did not remove duplicates.
    Any ideas what l am doing wrong, this is the query:

    SELECT DISTINCT tbl_SSL_UniquePArts.PartNumber
    FROM tbl_SSL_UniquePArts;

    Any help is appreciated.

    Regards
    Justin

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Removing duplicates from tables (2000)

    This query does not try to remove duplicates. All it does select the distinct PartNumbers.

    To actually remove them you can turn it into a MakeTable query, then use the new table it makes.

    At the query grid go to Query.. Make Table Query in the menus

    Of course if there are other fields in your table you need to also add them to the query.

    And also you need to actually Run the query by going to Query Run.
    Regards
    John



Posting Permissions

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