Results 1 to 4 of 4
  1. #1
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46

    When Tables Collide (Joining Two Tables)

    My Office has a perverted sense of data... We have been using two different Access DB's that contain some of the workers information (see fake DB) in one DB's and other information in the other DB. Now they want to combine the data from both DB's and use one centralized DB for the good of the group (finally!). Problem is, I am not sure how to combine the missing parts in Table1 and Table2 in to one Composite Table that contains all of the information from both DB's.

    I don't want to do this manually if I don't have to there are over 2000 records, but I understand if I have to manually change the ID Field and make those match in the tables. Both tables have incomplete data that needs to be combined in to a separate table. I am just not sure where to get started or is this is even possible. Any help with this would be great. Thanks.



    Please see attached file.
    CombineThese.accdb

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If you are using autoNum IDs,then create a field for OldID to store the auto id from the other table.

    putting table2 records in table1 will create new autoIds. Now they have new and old IDs.
    use the OldID joined to the sub tables to load the sub tables with the NewIDs. Then port the sub tables over.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would not use "ID" to match records if the "ID" field is an autonumber field. Because you have two separate databases/tables, there is nothing that guarantees that the ID number for "Martin" in dB1 will be the same as "Martin" in dB2.

    Is there an Employee ID field that is assigned to an employee?

    Otherwise, pick one databases/table as the "master" employee table, write a lot of VBA code find and compare the fields in each field for each record from dB2 with dB1 and update/insert a record as needed.

  4. #4
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    Thanks for the responses, I think that I am going to have a long, long, long Saturday... LOL... ugh. I am going to mark this as solved (But I know this is unsolved)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Joining tables help
    By grewpar in forum Access
    Replies: 7
    Last Post: 10-07-2014, 12:11 PM
  2. Joining two tables
    By rspai in forum Queries
    Replies: 7
    Last Post: 05-22-2012, 12:21 AM
  3. joining tables
    By frozendmj in forum Queries
    Replies: 5
    Last Post: 04-19-2011, 01:45 PM
  4. Help - Joining Tables..
    By fabian24 in forum Forms
    Replies: 3
    Last Post: 02-17-2011, 03:43 PM
  5. Joining tables two tables and another table that is not
    By DevintheDude in forum Database Design
    Replies: 0
    Last Post: 09-12-2007, 08:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums