Need help writing a efficient query

[Netooi]

Ok, I have two tables...
Table One and Table Two for lack of a better name.

Table One has a field called IDNumber.
It contains text fields of numbers such as "123456789"

Table Two has two fields in which these text numbers can be stored. We will call them NumberOne and NumberTwo

I need to write a query or code that will in a somewhat reasonable amount of time Pick all of the IDNumbers from TableOne that are not found in either of TableTwo's fields...

To clarify...
If "123456789" shows up at all in TableTwo, no matter which of the two columns, one or both of them, it should not show up in the result list.

Thanks if anyone can help,
Kevin

 

[LittleSmudge]

You need TWO queries

Query 1 called SelectNotWanted
SELECT TableOne.PrimeKey, TableOne.DataLine
FROM TableOne, TableTwo
WHERE (((TableOne.DataLine)=[TableTwo].[Field1] Or (TableOne.DataLine)=[TableTwo].[Field2]));


Query 2 is then
SELECT DISTINCT TableOne.PrimeKey
FROM SelectNotWanted RIGHT JOIN TableOne ON SelectNotWanted.PrimeKey = TableOne.PrimeKey
WHERE IsNull(SelectNotWanted.PrimeKey) ;



QED

G LS