Results 1 to 9 of 9
  1. #1
    samdahlr is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2014
    Posts
    28

    Delete duplicate records

    Hi,

    Here's a tricky one! I have 5 very large databases that I am merging together via UNION SQL query. There are common records between the databases although one database may have the contacts "EmailAddress" and one database may not. When I use UNION it automatically deletes duplicate records, but the records are not exactly duplicate as in one databases there may be an empty field for a record that the other database has. Is there a way I can tell Access to ignore the empty fields and recognize when one row has exactly the same data as another (excluding empty fields) and deletes it? Is there any other way I can handle this?

    Thank you!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,011
    If you are talking about the 'address book' problem, where the person's name may be off 1 character, or address , or city....there's no way to tell which is the correct one.
    UNLESS you have a hard key...(like State code, in the a USA state table), but people's address's dont have this so no way to tell which is the correct one/unique one.

    Depending on your fields.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,767
    sam,

    In database duplicates are duplicates. There are not duplicates sort of. As ranman said How do you know which is the correct value?
    It might be helpful if you could show us some sample data and show us exactly a few of.your "duplicates". Addresses and modifying/verifying/validating of these have been careers for many people.
    I wouldn't be too quick to try UNIONsk. I think you need to focus on What makes the correct version correct?
    Good luck.

  4. #4
    samdahlr is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2014
    Posts
    28
    Here is a much smaller sample of what I am working with. As you can see it copied the same person twice because there is missing data one of the tables.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,767
    no data, no attachment????

  6. #6
    samdahlr is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2014
    Posts
    28
    For some reason it is not letting me attach the file. It says it is too large but it is actually small.

  7. #7
    samdahlr is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2014
    Posts
    28
    am I not allowed to attach a database more than 500kB?

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,767
    Did you do a Compact and Repair, then zip the file.

  9. #9
    samdahlr is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2014
    Posts
    28
    Here is the attachment.

    Thank you.
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-08-2017, 09:04 AM
  2. Delete duplicate records and unite results
    By rndmxy2k13 in forum Access
    Replies: 1
    Last Post: 09-24-2013, 04:21 AM
  3. Replies: 3
    Last Post: 07-14-2013, 08:24 PM
  4. Replies: 2
    Last Post: 04-13-2012, 12:53 AM
  5. Delete duplicate records
    By Zukster in forum Queries
    Replies: 1
    Last Post: 08-26-2009, 03:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums