Compering records from 2 identical tables from structure

[Aydan]

I wonder if it would be possible to compare data in records from 2 structural indentical tables and see only the records which are diffent from in the other table (only from one table at the time)

 

[BillPower]

If the tables have identical columns, you could try using a UNION query:

SELECT "Table1" AS TableName, *
FROM MyTableName1

UNION SELECT "Table2" AS TableName, *
FROM MyTableName2;

Substitute your table names for MyTableName1 and MyTableName2.

I have added "Table1" AS TableName and "Table2" AS TableName above as identifiers.

You can now query this query to get the info you want.

Regards Bill

 

[jiqjaq]

This is similar to finding duplicates except the condition is <> rather than = and you need OR's instead of AND's.

SELECT *
FROM T1 INNER JOIN T2 ON T1.a <> T2.a OR T1.b <> T2.b OR T1.c <> T2.c OR...

This will give you everthing in the first table that's not in the second. You must list each field in the table in the JOIN.

 

[PWise]

if you can base this on one field such as name try use the find unmatched query wizard
if you have to compare to more then field
in the query grid select * ,Field1ToCompare&Field1ToCompare&Field1ToCompare&.. as expr1 from table1
in the criteria row in expr1 column not in (select Field1ToCompare&Field1ToCompare&Field1ToCompare&.. as expr1 from table2)
run the query and it should give you the results you want

 

[cmmrfrds]

If this is for a user to compare the data it might be easier to do it in on a Form.

Data source for the Form.
Select table1.*, table2.* from table1, table2
where table1.key = table2.key

Conditional Formattting in Form on the fields you need to compare. Conditional Formatting in under Format on the menubar.

 

[Aydan]

Hi Bill, I have about 20 fields in this tables, so a Union query would take me to long to seek for the differences. The records are almost the same in this 2 tables except that some records are modified in the second table and there are some additions done, also in the second table.I Think that the solution of jiqjaq is the most efficient way to try.
Is the proposed solution of pwise not almost the some thing as jiqjaq only it makes us of the QBE grid?