Compare and/or combine two Access database tables

  • Thread starter Thread starter Studiot
  • Start date Start date
  • Tags Tags
    Compare Database
Click For Summary

Discussion Overview

The discussion revolves around the challenge of combining or comparing two separate copies of an MS Access database that have been independently modified. Participants explore methods to identify differences and merge data without losing any work.

Discussion Character

  • Technical explanation
  • Debate/contested
  • Homework-related

Main Points Raised

  • One participant suggests writing a script to compare the two tables line by line, adding unique entries from one table to the other if they do not already exist.
  • Another participant proposes using an append query to add rows from one table to the other while ignoring duplicates.
  • A concern is raised about the append query's effectiveness, as it may not account for records that have been updated in one copy but not the other, necessitating a method to retain the latest version based on date.
  • One participant expresses uncertainty about using SQL for comparison, indicating a lack of recent experience with Access.
  • Another participant acknowledges the simplicity of the task but admits to needing time to work through the process due to a lack of recent practice.

Areas of Agreement / Disagreement

Participants present multiple approaches to the problem, with no consensus on the best method to combine or compare the tables. The discussion remains unresolved regarding the most effective solution.

Contextual Notes

Participants express uncertainty about the capabilities of append queries in the context of updated records and the potential need for additional logic to handle date-based updates.

Studiot
Messages
5,440
Reaction score
11
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.
 
Computer science news on Phys.org
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?
 
Hello Jared, welcome back, hope the exams or whatever went well.

Yes please coding/ programming is not my bag.
 
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.
 
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.
 
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.
 

Similar threads

ArsTechnica Article on Backing Up Your Stuff via BeeStation
  • · Replies 6 ·
Replies
6
Views
2K
Viewing same Image/Picture Independently
  • · Replies 12 ·
Replies
12
Views
2K
MHB Understanding Primary and Foreign Keys in Database Relations
  • · Replies 7 ·
Replies
7
Views
1K
Graduate Crystallographic representation of a material, two sources seem very d
  • · Replies 2 ·
Replies
2
Views
1K
Two equations "combined" don't give the desired result
  • · Replies 3 ·
Replies
3
Views
1K
Two-layer dielectric, four-capacitor model vs three-capacitor model
  • · Replies 5 ·
Replies
5
Views
1K
Undergrad How to compare two data sets with multiple samples
  • · Replies 8 ·
Replies
8
Views
2K
Undergrad What is the latest discovery in tetraquarks by LHCb?
  • · Replies 4 ·
Replies
4
Views
3K
Modern Digital Audio: Understanding Dither and Oversampling in Hi-Res Age
  • · Replies 17 ·
Replies
17
Views
6K
LaTeX Best way to merge columns and rows in tables with column separators
  • · Replies 0 ·
Replies
0
Views
3K