Compare and/or combine two Access database tables

In summary, two people have been typing into separate copies of the same MS Access database for some time. Jared suggests using an append query to compare the two tables and merge the differences if they are different.
  • #1
Studiot
5,440
9
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
  • #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?
 
  • #3
Hello Jared, welcome back, hope the exams or whatever went well.

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

1. How do I compare two Access database tables?

To compare two Access database tables, you can use the "Find Duplicates Query" tool. This tool allows you to select two tables and identify any matching records between them. You can also use the "Table Analyzer" tool to compare the structure and data types of the two tables.

2. Can I combine two Access database tables into one?

Yes, you can combine two Access database tables into one by using the "Append Query" tool. This tool allows you to select two tables and append the data from one table onto the end of the other table. You can also use the "Union Query" tool to combine data from two tables into one table with unique records.

3. How do I merge data from two Access database tables?

To merge data from two Access database tables, you can use the "Merge Tables Wizard" tool. This tool allows you to select two tables and specify which fields you want to merge. You can also use the "Update Query" tool to update records in one table with data from another table.

4. Can I compare and combine tables with different structures in Access?

Yes, you can compare and combine tables with different structures in Access. The "Linked Table Manager" tool allows you to link tables from different databases or even from Excel spreadsheets. Once the tables are linked, you can use the comparison and combination tools mentioned above.

5. Is there a way to automate the process of comparing and combining Access database tables?

Yes, you can automate the process of comparing and combining Access database tables by creating a macro or using VBA (Visual Basic for Applications) code. This will allow you to save and run the comparison and combination steps with the click of a button, rather than manually performing them each time.

Similar threads

  • Set Theory, Logic, Probability, Statistics
Replies
7
Views
612
  • Computing and Technology
Replies
12
Views
1K
Replies
2
Views
819
  • Introductory Physics Homework Help
Replies
3
Views
335
Replies
9
Views
1K
Replies
5
Views
879
  • High Energy, Nuclear, Particle Physics
Replies
4
Views
2K
  • Materials and Chemical Engineering
Replies
12
Views
432
  • MATLAB, Maple, Mathematica, LaTeX
Replies
1
Views
2K
  • Science and Math Textbooks
2
Replies
38
Views
6K
Back
Top