Khasimi said:
Hi,
I have two identitical tables in Access. One table contains the data
enter
by the data entry operator and another has same data but downloaded from
DB2
table through ODBC. I need the compare these two tables and create
another
table which contains the differences in each record. Structure and field
names are same in both the table.
Thanks in advance.
Here are some examples using two copies of the Employees table from the
"Northwind" sample database ...
This query will return any records in Employees1 with no matching record in
Employees2 ...
SELECT Employees1.*
FROM Employees2 RIGHT JOIN Employees1 ON Employees2.ID = Employees1.ID
WHERE (((Employees2.ID) Is Null));
.... while this query will return any records in Employees2 with no matching
record in Employees1 ...
SELECT Employees2.*
FROM Employees1 RIGHT JOIN Employees2 ON Employees1.ID = Employees2.ID
WHERE (((Employees1.ID) Is Null));
You could combine the two results in one union query ...
SELECT Employees1.*, "1" AS Source
FROM Employees2 RIGHT JOIN Employees1 ON Employees2.ID = Employees1.ID
WHERE (((Employees2.ID) Is Null))
UNION
SELECT Employees2.*,"2" AS Source
FROM Employees1 RIGHT JOIN Employees2 ON Employees1.ID = Employees2.ID
WHERE (((Employees1.ID) Is Null));
Then you need to find records that exist in both tables but with differences
in one or more fields. For that you need something like the following ...
SELECT Employees1.*, Employees2.*
FROM Employees1 INNER JOIN Employees2 ON Employees1.ID = Employees2.ID
WHERE [Employees1].[Company]<>[Employees2].[Company] OR [Employees1].[Last
Name]<>[Employees2].[Last Name] ... etc., etc, for each field that you need
to compare.
