compare all the records of all the fields of two tables

  • Thread starter Thread starter Frank Dulk
  • Start date Start date
F

Frank Dulk

Does some exist it sorts things out of to compare all the records of all the
fields of two tables and to see that this different one among the tables?

Each table has 90 fields


example

table 1 table 2 table1
table2
field1=a field1 = b field2=5 field2=6
field1=u field1=k field2=9 field2=8
field2=10
field2=50
 
It's not easy, and with 90 fields, this may not work well. But one way is to
set up a query that you can use to see if the table1 records aren't equal to
the table 2 records (the ones stored in a table,
right?).

Essentially, you create a query that is based on the two tables. Join every
field from the first table to its counterpart field in the second table.
Make each join a LEFT JOIN from the first table to the second table. In the
SQL statement for this query, wrap the Nz function around each joining
clause:
tablename LEFT JOIN queryname ON Nz(table1.field1,"") =
Nz(table2.field1, "") AND Nz(table1.field2, "") = Nz(table2.field2,
"") AND ....

This will cancel out Null values that would otherwise cause nonmatching of
records.

Then put all the fields from table 2 onto the design grid; set the
"Criteria:" expression of the primary key field(s)to Is Null. Then put all
the fields from table 1 onto the design grid.

This query will return the records that don't match. Note that, with 90
JOINS, it may run very slowly.
 
Did I try to do the one what you mentioned more I didn't get, could you send
me a small example, with some fields?

Please.
 
Something like this:

SELECT Table1.*, Table2.*
FROM Table 1 LEFT JOIN Table2
ON Nz(Table1.Field1, "") = Nz(Table2.Field1, "") AND
Nz(Table1.Field2, "") = Nz(Table2.Field2, "") AND
Nz(Table1.Field3, "") = Nz(Table2.Field3, "")
WHERE Table2.Field1 Is Null;
 
But where is the records of the table2

wanted a resulted like this:

eg:

table1 table2 table1 table2
field1=4 field1=5 field2=8 field2=9
field1=8 field1=7 field2=6 field2=23
field2=9 field2=14
 
OK - but you're not identifying to us what is the "linking" field that
should have the same value in both tables.

If what you want is to show the records of both tables, then the query
example that I gave probably could be used as a subquery to another
query...something such as this (you'll need to modify based on actual field
names and joining fields, etc.):

SELECT table1.*, table2.*
FROM table1 LEFT JOIN table2
ON Nz(Table1.PrimaryKeyField, "") = Nz(Table2.ForeignKeyField, "")
WHERE Nz(Table1.PrimaryKeyField, "") IN
(SELECT Table1.PrimaryKeyField
FROM Table 1 LEFT JOIN Table2
ON Nz(Table1.Field1, "") = Nz(Table2.Field1, "") AND
Nz(Table1.Field2, "") = Nz(Table2.Field2, "") AND
Nz(Table1.Field3, "") = Nz(Table2.Field3, "")
WHERE Table2.Field1 Is Null);

--
Ken Snell
<MS ACCESS MVP>

But where is the records of the table2

wanted a resulted like this:

eg:

table1 table2 table1 table2
field1=4 field1=5 field2=8 field2=9
field1=8 field1=7 field2=6 field2=23
field2=9 field2=14
 
That is what I've posted. It would appear that I am not understanding what
you want.

How about posting sample data from records from table1 and table2, and show
which records should result and which ones should not result from the query.
 
But I want to show only the records different from the tables and of the
same fields.
 
see what intend:

table1 table2
field1=1 field2=58 field1=1 field2=5
field1=2 field2=96 field1=9 field2=30
field1=3 field2=600 field1=10 field2=11
field1=41 field2=1 field1=41 field2=3


result would be:

table1.field1 table2.field1 table1.field2 table2.field2
0 0 58 5
2 9 96 30
3 10 600 11
0 0 1 3
 
Yes, but your post mentioned that you have 90 fields. I don't see that many
fields here...to try to help, can you give more details about the other 88
fields?



see what intend:

table1 table2
field1=1 field2=58 field1=1 field2=5
field1=2 field2=96 field1=9 field2=30
field1=3 field2=600 field1=10 field2=11
field1=41 field2=1 field1=41 field2=3


result would be:

table1.field1 table2.field1 table1.field2 table2.field2
0 0 58
5
2 9 96
30
3 10 600
11
0 0 1
3
 
But what described oh it is just with some fields, but if gives right in
those it is also going of the right in the other ones.
 
Back
Top