Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merging Data (Access97)

    Does anyone have any suggestions about the best way to combine data from several different tables from one database into another? The situation came about because a desktop shortcut was deleted and a new one created, but the new shortcut pointed to the wrong file. In the meanwhile data was being entered in 2 separate places, which means that neither has all the information it should. The layout (tables, queries, etc.) of both files are identical, only the data is different. Is there a relatively simply (fingers crossed) way of taking the information from one and importing it into the other? Thanks for your help!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Merging Data (Access97)

    There is no clearcut recipe for this kind of problem. If you want to do it yourself, you can create links in the database you want to keep to the tables in the other database, and create queries to find the differences. You can use the Find Unmatched Query Wizard to help with this. If you have a "date modified" field in the tables, that would make things easier, but in the end, a certain amount of manual checking is inevitable.

    There are third-party utilities that can help synchronize databases, for example CDB Database Comparator.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging Data (Access97)

    The information that has been put into the 2 locations...is it along the lines of creating new records with information? Or editing existing info? You may want to muddle with making copies of the two databases and running update or append queries...update queries if the info has just been changing and you can like them by assigning identical primary keys or append queries if new records have been created. If you're just trying to bring in information onto one "table" from many, I'm assuming they all relate to one specific thing (a person, a company, etc..) and that person or company should have a primary key. You could then create a query pulling the information from the linked tables....I'm not at all familiar with the abilities of Access 97, I don't know what features it has or doesn't have, but assuming it can do all of the above, I'd try some of those options... but like Hans said, without a date modified cell or some other identifier that says "HEY! I've been changed!", there is definitely going to be some manual checking...if only one table has been changed by one source, and another table has been changed by another source, then an update query would answer your problems....if everyone's been muddling in everything, then everyone *might* be a little upset when you try to return to one database. [img]/forums/images/smilies/biggrin.gif[/img]
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging Data (Access97)

    Thanks guys for your responses. I thought as much, but had to ask. I'll definitely make a copy of the database before trying anything. The database contains patient data so I'm thinking just a couple of tables were probably changed (new patients added: name, dob, etc.) and details regarding their visit to the hospital. So perhaps appending the missing data may work. I'll let you know how it turns out & thanks again for your suggestions.

Posting Permissions

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