Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    merge tables (2002)

    Hi all, quick question: 3 separate tables, all with same fields and structure (these tables are in physically separate databases at 3 different hospital sites). What is the best way to merge them into one "master" table? This needs to be done every month or so. I only want new records to be merged. What are the implications re: primary keys? Thanks, Van

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: merge tables (2002)

    I assume all 3 tables can be added to at each location?

    When you say into one "master" table, does it then replace the table at each site, or is this a complete new table?

    You can use the query to append records that are not in the output table. I asume you use all fields (except any autonumber fields) in the record to determine if it exists already in the target table.

    Is this to be done when the databases are closed?

  3. #3
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: merge tables (2002)

    Hi Pat, yes each site has their own data and no, the "master" table would not overwrite the site tables. I 'm wondering if an autonumber is really needed at all? I was thinking of using a combination of surname, given, and site name as the PK. When i tried using an autonumber as the PK the data from the site tables just keep getting added again to the "master" table as if it was new.....b/c of the new autonumber assigned by the "master" table. Van

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: merge tables (2002)

    It's up to you what you define as new records, i would use all fields (except autonumber).

    I would do the following:
    1. clear out the "master" table
    2. append all records from table 1 to the master table
    3. append those records from table 2 that are not in the master table
    4. append those records from table 3 that are not in the master table
    5. copy the master table to the other 2 databases having deleted it from the 2 target databases first

    Hops this helps

  5. #5
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: merge tables (2002)

    thanks muchly for the help and hand-holding Pat.
    Regards, Van

Posting Permissions

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