SQL to find FileNum in one table not in another

[Guest]

I am now ready to start validating the data in my database. It's extracted
from several Excel Tables. One problem I'm encountering is that I need a
One-to-Many relationship between "EmpData" (One) and "CurrentData" (Many).
But I've got more records in "EmpData" than in "CurrentData" There needs to
be at least one record in "CurrentData" (715) for every record in "EmpData"
(734).

I've already run
SELECT COUNT (*) FROM eachtable
and
SELECT COUNT (DISTINCT *) FROM eachtable
to verify that there are no duplicates.

I'd like to build a query (preferably with SQL 'cuz I'm an old command-line
programmer who hates GUIs) that will find the records in "EmpData" without a
corresponding record in "CurrentData".

The PriKey in "EmpData" is FileNum. It is the relationship field (ForKey) in
"CurrentData".

Thanks for all your help!

 

[John Spencer]

To get records in EmpData that have no corresponding record in CurrentData

SELECT EmpData.*
FROM EMPData LEFT JOIN CurrentData
ON EmpData.FileNum = CurrentData.ForKey
WHERE CurrentData.ForKey is null



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

That returned the differing records. Thank you very much!
--
Adios,
Clay Harryman

To get records in EmpData that have no corresponding record in CurrentData

SELECT EmpData.*
FROM EMPData LEFT JOIN CurrentData
ON EmpData.FileNum = CurrentData.ForKey
WHERE CurrentData.ForKey is null



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..