Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete (almost) duplicate records (Access 2003)

    I am struggling with the ability to delete records in access that are duplicates, except one field. The attached database has records with a name, company, an email addres. One record is new for each set and one is old. I want to get rid of the old based on the name criteria, such as

    Select all records where the email is identical AND the company is identical but where the name is either Mary Lamb, Joe Joe, or Earl Funk. I also want to leave any records that are truly unique. So if there was one IBM email for Mary Lamb, I do not want to lose it.

    This seems very easy but I cannot seem to accomplish the task. I would appreciate any help.

    Edit: I have tried many things, including append queries, group totals, etc, but they do not work because of the randomness of the data and how it exists. This is the last direction I went:
    SELECT *
    FROM Table1 AS A, Table1 AS B
    WHERE A.company=B.company And A.emailaddress=B.emailaddress And A.rmname<>B.rmname;

    The results were interesting, but would leave me with editing to get rid of the bad names and does not include the single records without any partners.

  2. #2
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete (almost) duplicate records (Access 2003

    I think you have the wrong post- it has a "Table1" and thats it.

  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: Delete (almost) duplicate records (Access 2003

    The db you posted only has a committees table and no others so I think you need to post it again.

    Added later...oops sorry my mistake . I opened the wrong file. Looking at it now!
    Regards
    John



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

    Re: Delete (almost) duplicate records (Access 2003

    If you find two records with the same email and company name, how would you decide which record to keep and which to delete?

    If you had a date created field, or last updated you could keep the most recent, but without that what?
    Regards
    John



  5. #5
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete (almost) duplicate records (Access 2003

    I do know the names, for instance "Mary Lamb". But it gets difficult because I do not want to lose her exclusive records, only the ones where the fields in her record are shared with another rmname. So I want to lose the Mary records where another name (like Steven Krunk) exists.

    I need to keep Mary if she is unique

    Else lose her to the other record.

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

    Re: Delete (almost) duplicate records (Access 2003

    Sorry

    It is still not clear to me exactly what you want. I thought you only wanted one person with any combination of email address and company.
    If you find two (or more0 people with the same email and company delete all except one of them at random.

    I would attach an autonumber key field then keep the record with the largest of these. I still have to work out how to do that, but I want to be sure this is what you want.
    Regards
    John



  7. #7
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete (almost) duplicate records (Access 2003

    From Table1, return Mary Lamb for ibm and not Steven krunk.
    From Table1, return Joe Joe for microsoft not B Cool.
    From Table1, return Earl Funk for hp not Evan Skunk.

    And I forgot to add a unique record for Mary lamb:
    company of "Home",
    the email address of home@home.com,
    the title of "Sir",
    the state of "PA",
    and the zip of "19000".

    I need that back, too in the same result set.

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

    Re: Delete (almost) duplicate records (Access 2003

    Here is a go at this.

    I use three queries.

    qryRepeats finds those records where the combination of company and email is not unique.
    qryToKeep finds the largest PersonID for each of these groups.
    qryDelete deletes any record where the combination of company and email is not unique, and the PersonID is not in qryToKeep.
    Regards
    John



Posting Permissions

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