Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Posts
    6

    Unanswered: finding duplicates

    i need to run a query which will run through a list of company names and delete any duplicate entries.

    i knwo there has got to be a way to dothis but i cant think of how. it is just a simple access 2002 database table.

    can anyone help here?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Re: finding duplicates

    to delete duplicate entries, you need to find them, save one of each, delete all the duplicate rows, and then insert the one of each back

    to find them and save one of each,

    select companyname into tempcompanytable
    from companytable
    group by companyname
    having count(*)>1

    delete the duplicates

    delete from companytable
    where companyname in
    (select companyname
    from tempcompanytable)

    insert the single rows back

    insert into companytable
    select companyname
    from tempcompanytable

    clean up

    drop tempcompanytable


    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Are you wanting to remove duplicates from a select query ? If so, you can use the keyword distinct.

    select distinct companyname from table

    So if your table have the following:

    CompanyName

    ABC
    CBS
    CBS
    CBS
    NBC
    ESPN
    ESPN
    CNN

    Then, the output would be:

    ABC
    CBS
    NBC
    ESPN
    CNN

Posting Permissions

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