Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Palmyra, Virginia, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merge and clean duplicates (Access 2000)

    We've got a flat file Access database serving as a card catalogue (sort of). Actually, we (unfortunately) now have two such databases. The 'real' one is maintained on the network and anybody can make updates. Unfortunately, one of our users somehow ended up with a local copy and has been using it for several weeks (months?). So, now we need to recombine them with as little human intervention required as possible.

    Each file has about 2000 records.

    There is a one field which is intended to be unique to each record (card catalogue number).

    Some records will be exact duplicates.

    Some records will have the same card catalogue number but other fields of the same record will have been changed.

    Some records in each database will be unique.

    There may be some records in the local database that have the same card catalogue number as a record in the main database but are not the same entry.

    So, anyhow, is there a way to merge the two databases, eliminate the second copy of records that are exact duplicates, and mark records that came from the local copy so that they can be easily compared to existing records in the master database?

  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: Merge and clean duplicates (Access 2000)

    I don't think you are goping to find any magic fix-all solution, but here is what I think I would do.
    Import the local db into the network one, but place records into a separate table.
    Add one new field to each of the tables, to be used as a marker field, indicating what needs to be done.
    Create a custom function that creates a recordset out of each table, sorted into catalogue id. Then loop through the two recordsets in tandem, comparing values, and use the marker field to mark records for deletion, keeping, further checking etc.
    At the end of each loop increment one or the other or perhaps both of the recordsets depending on what you found.
    When you have finished run some delete queries, append queries to deal with those marked, and manually decide what to do when you have two posible values. etc
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Palmyra, Virginia, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge and clean duplicates (Access 2000)

    Aaaww! I was really hoping for 3 lines of magic VBA code. <img src=/S/cauldron.gif border=0 alt=cauldron width=20 height=20> And your solutions will require me to learn quite a bit more than I know now. But, that is part of the joy of this board. I

    If I search help for 'custom function' and 'recordset', will I find hints on what I need to learn? Are those standard terms?

    Thanks for the input.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,512
    Thanks
    3
    Thanked 46 Times in 46 Posts

    Re: Merge and clean duplicates (Access 2000)

    To add to what John suggested, you can do part of this by queries, and if your table structure is very simple, all of it by queries. But it isn't much fun.
    Step 1 is to identify which records only exist in one copy or the other, and that's pretty easy to do with the Query Wizard that creates Unmatched Record queries. The bigger challenge is figuring out which records need to be kept, which need to be deleted, etc. And that can be a problem because some records may have been added to only one database, and other records may have been deleted in only one database.
    Step 2 is to identify which records are the same in both databases - that lets you simply declare them good and proceed.
    Step 3 is to figure out what is different with each of the records that are left. Some of them may be cases where two different records were added in the two databases. In other cases it may be the result of one or both parties editing a record that started out the same. If you use a timestamp date field to determine when a record was last edited, that helps considerably. If you don't, manual verification of the data is about the only way to determine which is correct - and you might want to add the timestamp for future problems of this ilk.

    I am somewhat familiar with the problem, as I just discovered that one of our clients had moved a back-end database to a new server, and connect about half of the staff to the new server, but the rest were still connected to old server. I was lucky, as there was very limited editing that had been done, aside from deleting about 1000 records in one database. Hope your situation turns out to be similar.
    Wendell

Posting Permissions

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