Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting Duplicate records (1)

    I am trying to delete duplicate records from a table. In the table the field that uniquely identifies the record is a social security number. All fields within the table are texted. I have tried a delete query using >1 as the criteria but it comes back >"1" since its a text field. I can't change the field becuase I will lose records if I try. Anyone have any suggestions on how to delete these records that have more than one associated with a particular number.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting Duplicate records (1)

    I don't think I understand. If the social security number uniquely identifies the record, how can you have duplicate records? If you don't have a unique index on that field but that's what you're trying to get to, create a new table with the same fields but with a no duplicates index on the social security number and then import the records from the existing table. That will keep any duplicates from getting into the new table.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jul 2003
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Duplicate records (1)

    Over the course of years people have entered in the same records twice so now I am trying to correct the problem.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting Duplicate records (1)

    There is no way to remove duplicates automatically, unless you can formulate criteria which of two or more records with the same SSN should be deleted. In theory those records should be completely identical, but in practice you will find that they differ in subtle (or not-so-subtle) ways; in the final analysis, a human must decide.

    You can use the Find Duplicates Wizard to locate duplicate records. Activate the Queries tab of the database window, click New, select Find Duplicates Wizard and the appropriate table, then click OK. The Wizard will explain what to do.

  5. #5
    New Lounger
    Join Date
    Sep 2004
    Location
    Durham, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Duplicate records (1)

    hans
    i have a table linked to data i import from a web vendor. each time i import for a key field (by horse name) the previous data is loaded (which i already have) along with the new data. consequently when i run my reports based on this table i get dups. how can i delte the duplicate rows in my table. the table is too large to delete rows individually. attached dummy mdb. file. perf date is the column that comes in each time i update.

    thanks

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Deleting Duplicate records (1)

    Why don't you import the data into a different table, then using that table delete entries from your other table where there is a horse in the imported table. After all this just use an append query to add data from the imported to the other table.

Posting Permissions

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