Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Deleting duplicates

Discussion in 'Information Technology' started by hms cat, Jul 28, 2006.

  1. hms cat

    hms cat
    Expand Collapse
    Guest

    Hi, I went through a lot of the answers to similar questions, but there are
    always issues, so I though I'd give my specific situation and hopefully get a
    working answer.

    I have two databases, 'Main' and 'Detail.' They are related by a column
    called 'id'. The Main and Detail databases have a relationship so that there
    is a + sign next the each row in Main, and clicking it will show you the
    information for that id # in Detail. In Main, there are these columns - id
    (which has a primary key), first name, last name, phone, fax, email.

    What I need is to delete all the rows in Main that have duplicates for both
    First Names and Last Names. I would like the kept one to be the one last
    entered (higher ID). I would also like to delete the corresponding row in
    the Detail database, if possible.

    Like I said, I've tried several suggestions I read, but there have always
    been issues like the delete query running for an insanely long time and never
    finishing (the database is only 6,000 records) or the columns for phone, fax,
    and email not being kept in the final database.

    Any help is greatly appreciated.
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Is Mahesh Bhatt considering deleting Meera's controversial kissing ... Breaking News Mar 4, 2005

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Simplest solution is to create a table without the duplicates.

    1. Make sure the Name AutoCorrect boxes are unchecked under:
    Tools | Options | General.
    Explanation of why:
    http://allenbrowne.com/bug-03.html

    2. In the Database window, select your table.
    Copy (Ctrl+C).
    Paste (Ctrl+V).
    Tell Access you want "Structure only", and choose a new name.
    Ultimately, this will hold the de-duplicated data.

    3. Create aquery that gives you unduplicated records.
    It will GROUP BY the critical fields, and give you Max of the ID.
    Save the query.

    4. Create another query that joins that to your original table.
    This one gives you all the fields, but only for the de-duplicated records.
    Change this into an Append query.
    Tell Access to save to the table created at step 1.
    Run the query.
    Check that the new table has the right records.

    5. Open the Relationships window (View menu.)
    Break all relationships to your original table.
    Delete the original table.
    Compact the database.
    Rename the new table to the same as the old if you wish.

    6. Now to delete the related records.
    The query will be something like this:
    DELETE FROM Table2 WHERE NOT EXISTS (SELECT ID FROM Table1 WHERE Table1.ID =
    Table2.ForeignID);

    7. Recreate the relationships.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "hms cat" <hms cat@discussions.microsoft.com> wrote in message
    news:45630166-9A96-4DD4-9D25-8D9314FDF3E6@microsoft.com...
    > Hi, I went through a lot of the answers to similar questions, but there
    > are
    > always issues, so I though I'd give my specific situation and hopefully
    > get a
    > working answer.
    >
    > I have two databases, 'Main' and 'Detail.' They are related by a column
    > called 'id'. The Main and Detail databases have a relationship so that
    > there
    > is a + sign next the each row in Main, and clicking it will show you the
    > information for that id # in Detail. In Main, there are these columns -
    > id
    > (which has a primary key), first name, last name, phone, fax, email.
    >
    > What I need is to delete all the rows in Main that have duplicates for
    > both
    > First Names and Last Names. I would like the kept one to be the one last
    > entered (higher ID). I would also like to delete the corresponding row in
    > the Detail database, if possible.
    >
    > Like I said, I've tried several suggestions I read, but there have always
    > been issues like the delete query running for an insanely long time and
    > never
    > finishing (the database is only 6,000 records) or the columns for phone,
    > fax,
    > and email not being kept in the final database.
    >
    > Any help is greatly appreciated.
     

Share This Page