Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merging (2000 and Xp)

    A question for your wizards: a client created a simple database and created copies to pass on to different departments. Those departments added the information that that was requested. Now what the clients wants to create one big database with all the information in it. The problem is that it is in 7 different database with the same structure. Is it possible to merge/import information from a database into another without creating a new table. End goal is to have one big database with all the information.

    I am tapped out of ideas.

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

    Re: Merging (2000 and Xp)

    You can create a "master" database with links to the tables in the other databases. You can then use append queries to add the information from the linked tables to the local table.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Canada
    Posts
    101
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merging (2000 and Xp)

    I would need to append all fields and one of those fields has a primary key. Therefore, appending would not work, or even copy and paste would give me trouble.
    Although you did get me thinking, however, the solution would not work.

    [img]/forums/images/smilies/smile.gif[/img] Got any other ideas I could try? [img]/forums/images/smilies/smile.gif[/img]

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

    Re: Merging (2000 and Xp)

    Is the following feasible, or is it too much work?
    <UL><LI>Add an extra field to each table, of type Number (Long Integer), named DatabaseID.
    <LI>Use a simple update query to set its value to 1 in all records in the first database, to 2 in all records in the second database etc.
    <LI>Set the primary key to the combination of the original primary key field and DatabaseID.
    <LI>If you have relationships between tables in one database, involving a primary key field, add DatabaseID to the linking fields.
    <LI>You should now be able to append the tables to the "master" tables while keeping the (composite) primary key.[/list](Next time, let the client take this kind of thing into consideration before creating the databases)

Posting Permissions

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