Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Compare and/or combine two Access database tables

  1. Feb 2, 2012 #1
    I have just discovered that two people have been typing into separate copies of 'the same' MS Access database for some while.

    It came to light when one said, "Why can't Jo see the changes I make?"

    Any suggestions on how to combine the two tables or at least compare them with a view to copy/pasting/appending the differences so that a great deal of work is not lost?

    The two copies are of the same database so all the fields are the same.
  2. jcsd
  3. Feb 6, 2012 #2
    If it was me, I'd write a script to open both tables, read a line from one and then iterate through the whole of the second table to check for a duplicate. If found, skip it. If not found, add to the end and generate a unique ID (or let Access do it if it's automatic).

    I'm pretty sure you could even use the SQL to compare the two. But I'm not entirely sure on that.

    Haven't used Access in a few years, but I can have a go at coding it roughly if you need it?
  4. Feb 6, 2012 #3
    Hello Jared, welcome back, hope the exams or whatever went well.

    Yes please coding/ programming is not my bag.
  5. Feb 6, 2012 #4
    Actually, simpler would be for you to use an append query.

    It will add the rows from one to the other and ignore duplicates. If it throws errors, ignore it.
  6. Feb 6, 2012 #5
    OK thanks, however I'm not sure that append will work since only one field in a record may have been changed, so the record may appear in both copies, but one may have an updated field. I would need the latest version by date in that case.
  7. Feb 6, 2012 #6
    You'll have to give me a day or so then so I can try and work through it.

    It's actually really simple, I just haven't done it in ages.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook