Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: Synchronizing two Access Tables

    Greetings,

    I have two member tables in AccessDB, but some of the field names are not the same, that I need to synchronize. Here is how they are structured: (Table 1 is old and Table 2 is new)

    Table 1 (900 records)
    ---------------

    ID
    GroupID
    First Name
    Last Name
    Email Address
    ----
    ----

    Table 2 (1100 records)
    ---------------

    ID
    GroupID
    FName
    LName
    Email
    PhoneNumber
    OldMember (Y/N)



    1. First I need to find out what records exist in Table 1 but not in Table 2 and add them to Table 2.

    2. Secondly, I need to then find all records in Table 1 that match the records in Table 2 and update the Email address in Table 2 with the data from Table 1.

    3. And third I need to then update Table 2 field "OldMember" if that particular record existed in the original Table 1.

    Can anyone give me some insight as to how best to accomplish this task?

    Regards,

    JeoParis

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's a bit of a list you've got there sir.



    Ok, I think you can get this done in two queries. I'm used to t-sql so I'll do my best to translate to jet-friendly syntax.

    First off:
    1. First I need to find out what records exist in Table 1 but not in Table 2 and add them to Table 2.
    .
    INSERT INTO [Table 2] (ID, GroupID, FName, Lname, Email)
    SELECT ID, GroupID, [First Name], [Last Name], [Email Address]
    FROM [Table 1]
    WHERE [Table 1].ID NOT IN (SELECT ID FROM [Table 2]);

    Next:
    2. Secondly, I need to then find all records in Table 1 that match the records in Table 2 and update the Email address in Table 2 with the data from Table 1.

    3. And third I need to then update Table 2 field "OldMember" if that particular record existed in the original Table 1.
    UPDATE [Table 2], [Table 1]
    SET [Table 2].[Email] = [Table 1].[Email Address], [Table 2].OldMember = 1
    WHERE [Table 1].ID = [Table 2].ID;

    Again, I'm not 100% on the specific syntax, but you should be able to get the idea of the logic behind it.

  3. #3
    Join Date
    Mar 2004
    Posts
    4

    Thanks!!!

    Oh, you know what... I spoke too soon. The isn't a unique ID on either of the tables ( for the record I didn't create this DB, I am just picking up form where someone else left off ) so the "ID" from my post needs to be dropped. Now, this really makes it tough without a unique.

    To really compare the records in each table I will need to use the "GroupID", "FirstName", and "LastName".

    JeoParis
    Last edited by jeoparis; 03-23-04 at 18:28.

  4. #4
    Join Date
    Mar 2004
    Posts
    4

    Thanks... But!!!

    My mistake... there isn't a unique ID.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well, that sucks. But it's not the end of the world.

    for the first statement you could concatenate the relevant fields and compare them that way:

    WHERE GroupID+[First Name]+[Last Name] NOT IN (SELECT GroupID+fname+lname FROM [table 2])

    Similarly you can do:

    WHERE [Table 1].GroupID = [Table 2].Group_ID AND [Table 1].[First Name]=[Table 2].[FName] AND [Table 1].[Last Name]=[Table 2].[LName];

    for the second query.

  6. #6
    Join Date
    Mar 2004
    Posts
    4
    What if there are instances of records in either table where the First/Last Name are spelled differently?

    JeoParis

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by jeoparis
    What if there are instances of records in either table where the First/Last Name are spelled differently?

    JeoParis
    Then there's no possible way you can do this other then by hand. If there's no consistent, unique data between the two tables, there is no way to describe to a computer how to link them.

    Sorry.

  8. #8
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    To see the records in Table1 that not in Table 2 use Select in Select




    SELECT Tab1.field1, Tab1.field2, Tab1.field3
       FROM Tab1
      WHERE
     Tab1.field1 Not In (SELECT Tab2.field1, Tab2.field2, Tab2.field3);


Posting Permissions

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