Tables Comparison

[xuewei]

Hello,


I have two tables in Access database with the same definition. For example, table1 and table2 have exactly the same data fields but have some different records. I want to find what records have been added, deleted, or changed in table2 compare to table1. Are there any methods to do the comparison?

Thanks


Xuewei

 

[AccessAce]

Have you tried a Find Unmatched Query?

In the query browser, click on new, then select Find Unmatched Query, follow the wizard to get the result.

Forgive me if I oversimplified here. Let me know if you need more help.

 

[xuewei]

Hi AccessAce,


Thank you for your response.

I tried your suggestion. Using the Find Unmatched Query Wizard, we can find records in one table that don't have related records in another table.

However, I want to find not only added records, but also changed and deleted records.

Do you have any suggestion on that as well?

Thanks

Xuewei

 

[AccessAce]

The unmatched query wizard will create a query with a left or right join between the specified tables, linked by a single field. If you reverse that join, you will get a list of missing records going the other way. You can then merge the results using a Union statement such as this:

SELECT Table1.ID
FROM Table2 RIGHT JOIN Table1 ON Table2.ID = Table1.ID
WHERE (((Table2.ID) Is Null))
UNION
SELECT Table2.ID
FROM Table2 LEFT JOIN Table1 ON Table2.ID = Table1.ID
WHERE (((Table1.ID) Is Null));

This will display a comprehensive list of all added and deleted records from either table.
If there is a change in a non key field, that's a little trickier. Let me work on that for you while you try the above thought.

 

[AccessAce]

Oops. In case you would like to know from which table the extra records are coming from,

SELECT Table1.ID, "Table1" AS Source
FROM Table2 RIGHT JOIN Table1 ON Table2.ID = Table1.ID
WHERE (((Table2.ID) Is Null))
UNION
SELECT Table2.ID, "Table2" AS Source
FROM Table2 LEFT JOIN Table1 ON Table2.ID = Table1.ID
WHERE (((Table1.ID) Is Null));

 

[xuewei]

Hi AccessAce,


Thank you very much for the solutions. I tried it, it works fine for the "add" and "delete". I just changed a little bit of the SQL:


SELECT Table1.ID, table1.Name, table1.Address,table1.age,"Delete" as Action
FROM Table2 RIGHT JOIN Table1 ON Table2.ID = Table1.ID
WHERE (((Table2.ID) Is Null))
UNION SELECT Table2.ID,table2.Name, table2.Address,table2.age,"Add" as Action
FROM Table2 LEFT JOIN Table1 ON Table2.ID = Table1.ID
WHERE (((Table1.ID) Is Null));


The table1 is the old table and table2 is the new table. This query can find the deleted records for table1 and added record for table2.

For the changed records, I am still thinking. Do you think I need to compare fields by fields since that is the only way to find chaned records?

Thanks

Xuewei

 

[AccessAce]

The thought I was just entertaing was to create a single string of the concantinated fields and compare them to each other. Queries don't like to have relationships defined more than one way, so the relationship should be on a single field, if you can consolidate the information in to a single field that is. You can always break the fields out later. If that is going to take to much work for the computer to do, I would add a Yes/No filed to each record and have it trip every time an update occurs. Then, when the info gets moved or compared with the other table, set the flag back to its default setting.

 

[xuewei]

Hi AccessAce,


Your suggestion is one possibility to find the changed records. I will try it later. At the same time, do you think if I can create a procedure and use VBscript to do that?


Thanks


Xuewei

 

[AccessAce]

VB would be a great way to do it. You would create two recordsets ordered by ID fields in a similar order. Start at the first records of each. This code assumes the ID fields are ordered numbers.

If t1.ID>t2.ID then
'records were deleted from t1
t1.movenext 'loop back and test again
if t1.id<t2.id then
'record was deleted from t2
t2.movenext 'loop back and test again
else 't1.id=t2.id e.g. they are the same record
for i = 1 to NumberOfFields
if t1.fields(i) = t2.fields(i) then
'no difference in field values
else
'there is a difference
end if
t1.movenext
t2.movenext
end if

Just a rough thought.

 

[AccessAce]

Found another error in the code.

After the first and secong checks, I'm advancing the wrong table. They should be the opposite table of what it typed. Sorry about that.

 

[xuewei]

Thanks a lot, AccessAce,

I think it should work and I will try it later. I will let you know the results after I figure it out.


Hava a great day!

Xuewei

 

[xuewei]

Hi AccessAce,


I figured out the method to detect changing:

SELECT Table2.ID, table2.Name, table2.Address, table1.age
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE table1.Name <> table2.Name OR table1.Address <> table2.Address OR table1.age <> table2.age;

Thank you for your help and nice to discuss with you.


Xuewei