Results 1 to 3 of 3

Thread: Two Databases

  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Two Databases

    Yep its happened. I built a quick and dirty database to enter contact details in and our admin populated it with 400 entries. She sent the complete database out and continued to work , then the phone call came in with I've updated your database........

    Is there a script or query that can compare the two database tables and extract the records with changed information from each.

    Thanks Simon

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Databases

    For what it is worth i would select one of the databases as the one to run with and import all tables/data/forms etc from the other.
    Once you have one db you can go about removing duplicates.
    I would create a third set of tables with identical structures and then create queries that select from the two donor tables a data set that is complete and without duplicates (do you have any keys ? if so then select the whole lot ) once you have this data set change the query to an append query and 'paste' into the empty table(s) - if there are keys set up (or indeed indexes that do not allow duplicates) then access will not append the rows that conflict with the data restrictions. this should be your combined dataset. so remove the original tables from the database. If you are not sure then keep the old tables handy in an archive database for when the user finds data missing !!
    good luck
    Trust The Force Luke !!

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Two Databases

    So you have two databases with the same tables only different table contents.
    I would do following:
    - take one db as master
    - link all the table of the other db to the master
    - write a query to search for the differences

    The query:
    Not one, but three
    I give examples with two fields:

    First obtain the differences:
    SELECT master.fld1, master fld2 FROM master, second
    WHERE master.fld1 <> second.fld1 AND master.fld2 <> second.fld2;

    Second check for nonexisting records in master:
    SELECT master.fld1 FROM master
    WHERE master.fld1 NOT IN
    (SELECT second.fld1 FROM second)

    Third check for non existing records in second:
    SELECT second.fld1 FROM second
    WHERE second.fld1 NOT IN
    (SELECT master.fld1 FROM master)

Posting Permissions

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