SQL Access Puzzle (identical tables, non matching pairs)

[Nay]

I am developing a database in Access and am trying to find if two
tables are identical. I was thinking of creating a query that finds all
non matching pairs in both tables. If results are found then the tables
do not match, if no results are found then the tables do match.

The tables are simple. One numeric ID field.

Any suggestions on the best method for, testing identical tables, or
how to build a find non matching pairs in both tables query, would be
appreciated.

 

[TC]

SELECT "In T1 but not in T2", ID
FROM T1
WHERE NOT EXISTS
(SELECT NULL FROM T2 WHERE T2.ID = T1.ID)
UNION ALL
SELECT "In T2 but not in T1", ID
FROM T2
WHERE NOT EXISTS
(SELECT NULL FROM T1 WHERE T1.ID = T2.ID)

or somesuch, I imagine!

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Brendan Reynolds]

This example uses two copies of the Orders table from the Northwind database
....

SELECT Orders1.OrderID, "Orders1"
FROM Orders1
WHERE Orders1.OrderID NOT IN (SELECT OrderID FROM Orders2)
UNION SELECT Orders2.OrderID, "Orders2"
FROM Orders2
WHERE Orders2.OrderID NOT IN (SELECT OrderID FROM Orders1)

 

[Nay]

Thats exactly what i was after. Its slow but it will do.
Thanks

 

[TC]

It absolutely /should not/ be slow, as long as you have primary-keyed
the tables correctly. It should be almost instantaneous.

What is the primary key of each table?

TC (MVP Access)
http://tc2.atspace.com