Comparing two tables and then show difference

[BukHix]

I have two tables with exactly the same fields. There are 5 fields named:

ID
JobName
JobNumb
Location
Title

One tables has 4193 records and the other has 6957. The smaller of the two tables is the actual table that my application uses. Some how a bunch of records were deleted. I have an old back up which is the table with the 6957 records.

Basically I am looking for a way to compare the two tables to see the records they do not have in common. Once I do that I will isolate them and merge them with my smaller table which should get me to the 6957 plus any that have been added since the delete.

Does anybody have any ideas of how to accomplish this? I hope I didn't make it to complicated in my description.

 

[mission2java_78]

Hi Buck,

Why not a comparison between both tables using recordsets?

Whatever is different you throw into a text file line by line. Once complete you open the text file and re-put everything that is supposed to go back into the table.

Simple but sweet.

Jon

 

[NJudson]

If I follow you correctly you could just use a unmatched query with the larger table as 1st table in query and smaller table as 2nd table in query. The result of this should give you everything in table1 that does not exist in table2. Then you can append the results of this to you other table. However, if your gonna do that then wouldn't it be easier to just copy the larger table over the smaller one?

 

[The_Doc_Man]

You could try something bizarre like this:

Import the backup table to your database under a new name, keeping the structure exactly the same as the working table. This is a GOOD thing to have because it makes the next step so much easier.

Assuming the ID field is unique, just write an Append query in which you append records from the backup table to the working table. You will get a warning that says that some records could not be imported because of key violations. It will ask you if you want to do the append anyway. It will create an error table telling you which records would have been duplicated. The query will append the records that didn't cause key violations. You can toss away the error table if you wish.

Viola, you have merged the tables.

Now, if the ID is NOT unique, you have a greater problem. If that is the case, continue with this thread. Also, if you wanted to do just a little more than this, explain it here.

 

[BukHix]

Thanks for the help everyone. While considering The_Doc_Man's solution I noticed that there was a pattern to the deletions that I hadn't noticed earlier. I am still in the process of restoring but my job was made much easier by the discovery.

Thanks again.