Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing Duplicate Records (2000)

    Hi All:

    I have a 500,000 record table, but there are 5 of each entry so really only
    100,000 unique records. I want to remove the duplicate data. I know in
    Excel, I would do a pivot table, but the pivot table in Access didn't seem to
    work the same.

    Any help would be greatly apprciated.

    Thanks,
    Craig

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

    Re: Removing Duplicate Records (2000)

    Make a copy of your table in case you don't get this right the first time.

    Do your records have a unique identifier- say an autonumber field field? If so create a grouping query that returns the min(imum) of this field when you group by enough of the other fields to be sure you identify the duplicates. The records with these id fields are the ones you want to keep.

    Join this query back to the original table using the id field and select all fields. this will return all the records you want to keep.

    Turn it into a make table query and it will make a new table containing jsut the records you want. If it seems OK, rename both table and just use the new one.
    Regards
    John



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

    Re: Removing Duplicate Records (2000)

    I thought of an easier way to do this.

    Stiil suggest you make a copy in case.

    Make a query on your table that selects all fields, then display the properties box , click in the top half of the grid so that the properties box display the properties of the query (rather than of one the fields) and set the "Unique Records" property of the query to yes. It will then select each record just once, assuming the records really are completely identical.

    You can then turns this into a Make Table query and the new table will again contain just one copy of each record.
    Regards
    John



  4. #4
    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 Duplicate Records (2000)

    From the Microsoft Access Help File:

    <font color=red>Delete duplicate records from a table (MDB)</font color=red>
    Note The information in this topic applies only to a Microsoft Access database (.mdb).

    Deleting duplicate records from a table requires two steps. First, you create a copy of the structure of the table that contains duplicates, and then make primary keys of all the fields that contain duplicates. Second, you create and run an append query from the original table to the new table. Because fields that are primary keys can't contain duplicate records, this procedure produces a table without duplicate records.

    Copy the table and make primary keys of fields with duplicates.
    <font color=blue>How?</font color=blue>

    In the Database window, click Tables under Objects.
    Click the name of the table you want to delete duplicate records from.
    Click Copy on the toolbar.
    Click Paste on the toolbar.
    In the Paste Table As dialog box, type a name for the copied table, click Structure Only, and then click OK.
    Open the new table in Design view, and select the fields that contained duplicates in the table you copied.
    Click Primary Key on the toolbar to create a primary key based on the selected fields.
    Save and close the table.
    Append only unique records to the new table.
    <font color=blue>How?</font color=blue>

    Create a new query based on the original table that contains duplicates.
    In query Design view, click Query Type on the toolbar, and then click Append Query.
    In the Append dialog box, click the name of the new table in the Table Name list, and then click OK.
    Include all the fields from the original table by dragging the asterisk (*) to the query design grid.
    Click Run on the toolbar.
    Click Yes when you receive the message that you're about to append rows.
    Click Yes when you receive the message that Microsoft Access can't append all the records in the append query. This transfers only unique records to your new table and discards the duplicates.
    Open the table to see the results.
    When you're sure the new table has the correct unique records, you can delete the original table, and then rename the new table using the name of the original table.

    <img src=/S/grin.gif border=0 alt=grin 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

Posting Permissions

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