Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003

    Unhappy Unanswered: Merge data from two identical databases

    Hi everyone!

    I have the following problem:
    I have several databases that are exactly identical as structure but have different data. All databases consist of 8 tables with relationships between them. I need to merge their data in one database.

    I'm not an expert so to keep the relationships between tables I do this in the following way:
    1) I copy table1.DB2 and paste the data into table1.DB1
    2) I take IDs from copied data in table1.DB1 and paste them into table 2.DB2 (but first I remove the relationships between table1 and table2 in DB2)
    3) Than I copy table2.DB2 and paste the data into table2.DB1 (this way I keep the relationships between those two tables)
    4) I repeat steps 1) - 3) for the rest of the tables

    This way I manage to do get the job done, but it's pretty hart and I'm sure that there is an easier way.

    So If someone can tell me how to merge data from two identical databases it would really help me.
    If there isn't a way to merge the whole database than I'll apreciate If someone can tell me how to copy just two related tables from DB1 to DB2.


  2. #2
    Join Date
    Sep 2003
    Caldes de Malavella, Spain
    What I would do is to link all the tables into one database and then use a Union query to merge them, assuming they all have the same fields. The SQL would look something like :

    SELECT * FROM TblOne


    SELECT * FROM TblTwo


    SELECT * FROM TblThree

    Etc. etc.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy

  3. #3
    Join Date
    Jun 2003
    Thanks for reply andybriggs ... but I still don't get it :-(

    I know that wit union query I can merge the data from two tables but what about the relationships between those two tables and the other tables?

    Let's take a simple example:
    We have two absolutely identical databases (exept for the data itsef).
    Every DB has two tables - for example Companies and Products, that are related in one-to-many relationship.
    As you suggested we link those tables in one DB. So naw we have one DB with tables: Companies1, Companies2, Products1 and Products2.
    If we make a union query we can merge the contents of tables Companies1 and Companies2 (we can do this with the other two tables too). So in the end we can make one table Companies and one table Products. But how can we tell which company produces which product? What will happen with the ID fields that bind the initial tables and how can we keep the relationships?

  4. #4
    Join Date
    Jun 2003
    Is's obvious that my question is very stupid but couldn't do it with union query

  5. #5
    Join Date
    Apr 2004
    South Arica
    If you are using the new tables in a form/report, create a third query that uses the two union queries. In the design view, you can set up temp relationships

  6. #6
    Join Date
    Apr 2004
    Sydney Australia
    Try this, similar to what you tried but a bit easier to do.

    Firstly, make a copy of DB1 so as to save yourself from foul ups

    Now import the tables from DB2, DB3 and so on into Copy of DB1.

    With "import" the table names will change from MyTable to MyTable1, MyTable2, MyTable3 and so on.

    After you have imported all the tables rename then like this:

    MyTable1 becomes 1MyTable
    MyTable2 becomes 2MyTable and so on. I will explain later why the name should be changed.

    I think you said there were 8 tables so you make 8 append queries.

    Lets say the 8 append queries Append 1MyTable to MyTable and 1YourTable to YourTable and etc.

    After you have run those 8 queries you then go back to each query in design View and from the Show Table for bring up 2MyTable. All you do now is use the Return or Enter Key to move along each Table Box in the query grid and then the table name will go black. Just type a 2 and then do that as you move to each table box and you will see 2MyTable has been inserted. This is because when you have two tables above the grid Access puts them in a drop down list on the table row. Hence the reason for changing the imported table names from MyTable2 to 2MyTable. After the new table name has been inserted then delete the table above the grid from the previous query.

    It will not take as long as it sounds.

    You might also make a little macro that has 8 open query actions. If you do this the usual warning boxes will come up that appear when running an append query.

    However, if you make the first action line of the macro as SetWwarnings and to No, the wanings will not come up.

    Last edited by Mike375; 04-30-04 at 17:24.

  7. #7
    Join Date
    Jun 2003

    Smile Thanks

    Thanks guys!

  8. #8
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    Your problem is a bit trickier then first appears, but perfectly doable.

    Appending all the data into one database is just step one.

    The second step is running update queries to re-establish proper relationships.

    eg, say you have two identicle tables, with two of the same key values. When you dump stuff into other tables that uses that number as a key, how is access supposed to know which record you're referring to?

    Anywho, I would use one of the append methods described above utilizing auto-number so you may re-key your fields. Then run update statements to set the old key value to the new key.

    You would have to do this one database at a time in temporary tables so you dont' hose existing relationships.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Jun 2003
    Thank you Teddy, and once more thanks to all the other boys and girls who replied!
    I've done it and it works fine!

Posting Permissions

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