Comparing two tablse - searching for orphans

[DrCadd]

My database has three tables. Two of them are named X3FROM2 and X3TO2. They represent tie points between drawings.


For every connect in the TO table there should be a corresponding incoming connection in the FROM table. So, lets say a record in the FROM table reads:
Point From/To From/To Dwg On Dwg <- these are the field names
A FROM LDAR-020 LDAR-019

there should be a corresponding record in the TO table that reads:
Point From/To From/To Dwg On Dwg <- these are the field names
A TO LDAR-019 LDAR-020

I need a query to identify entries in either table that do not have a corresponding record. I'm clueless. Attached is my table.
Thank you.

[alansidman]

This query will give you all entries in table FROM with no corrresponding entry in the TO table

SELECT X3FROM2.Point, X3FROM2.[From/To Dwg], X3TO2.[From/To Dwg], X3FROM2.[On Dwg], X3TO2.[On Dwg]
FROM X3FROM2 LEFT JOIN X3TO2 ON X3FROM2.[From/To Dwg] = X3TO2.[From/To Dwg]
WHERE (((X3TO2.[From/To Dwg]) Is Null));

This query will give you the opposite

SELECT X3FROM2.Point, X3FROM2.[From/To Dwg], X3TO2.[From/To Dwg], X3FROM2.[On Dwg], X3TO2.[On Dwg]
FROM X3FROM2 RIGHT JOIN X3TO2 ON X3FROM2.[From/To Dwg] = X3TO2.[From/To Dwg]
WHERE (((X3FROM2.[From/To Dwg]) Is Null));

[DrCadd]

Thank you very much!

[DrCadd]

Your query seemed to work correctly on the first table I tried it on. However, when I tried it on a new database today it gave me a lot of false positives. Attached is the new database.Cont Check.zip

[alansidman]

I think that you have the Is null in the Orphans in the TO table in the wrong field. try this.

SELECT X3FROM2.Point, X3FROM2.[From/To Dwg], X3TO2.[From/To Dwg], X3FROM2.[On Dwg], X3TO2.[On Dwg]
FROM X3FROM2 RIGHT JOIN X3TO2 ON X3FROM2.[From/To Dwg] = X3TO2.[From/To Dwg]
WHERE (((X3FROM2.[From/To Dwg]) Is Null));