Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    70

    Unanswered: Duplicate fields

    Hi,

    How would I check a table for duplicate fields and remove the duplicate ones? Let's say a City table with 1 field. If there are 2 or more cities with the same name, I want to remove the duplicate one.

    Thanks,

  2. #2
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132

    Re: Duplicate fields

    Originally posted by vbgladiator
    Hi,

    How would I check a table for duplicate fields and remove the duplicate ones? Let's say a City table with 1 field. If there are 2 or more cities with the same name, I want to remove the duplicate one.

    Thanks,
    There are different solutions to this.
    1-Make cities a Primary Key.
    (this way it can never be duplicated)
    2-If thats not a primary key . then just Make A query and view that Query in report telling you which are duplicate values.
    3- You can make that query by relating it to main or Master table.
    hope this puts u on right direction.
    cheers

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you want to do this in a query, you will need some sort of criteria to determine which of the two records to keep. Lets say you have a autonumber field on the table (you can add one temporarily if necessary):

    DELETE DISTINCTROW CityTable.*
    FROM CityTable
    INNER JOIN CityTable AS CityTable_1 ON CityTable.City = CityTable_1.City
    WHERE CityTable.AutonumberField>[CityTable_1].[AutonumberField];

    Make sure your delete isn't going to cascade to related tables!

    blindman

Posting Permissions

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