What's Wrong With This MS Access Query?

[AGx-07_162]

Can anyone tell me what's wrong with this query?

INSERT INTO Employees ( Title, FirstName, LastName, Company, Department, Mailstop, Extension, City, State, Bldg, Floor, DirList, Prefix, Notes, Phone, importID, importDate, [Desc] )
SELECT Title, FirstName, LastName, Company, Department, Mailstop, Extension, City, State, Bldg, Floor, DirList, Prefix, Notes, Phone, importID, importDate, [Desc]
FROM dbo_Employees
WHERE dbo_Employees.importID NOT IN (SELECT Employees.importID FROM Employees);

I'll swear that this query worked fine when I first wrote it but when I came back to use it again this week it wont work. I don't get any errors but it wants to update 0 Rows.

Now, I am able to easily look at the data in the Employees table and verify that there are records in the dbo_Employees table where the dbo_Employees.importID number is NOT IN Employees.importID. Can anyone explain?

 

[KrazyMe]

Are the fields the same data types ? (Just to make sure...)
Try NOT EXISTS instead of NOT IN too.

 

[AGx-07_162]

Are the fields the same data types ? (Just to make sure...)
Try NOT EXISTS instead of NOT IN too.

Yes. Each field is the same data type. The query works fine if I remove the WHERE clause, in which case it imports everything. I will try using NOT EXISTS and report back. Thanks for the info.

 

[AGx-07_162]

I figured out what the problem was. The query itself was fine, it was the table that was the problem. NOT IN won't return results when there are any NULL values. The field in the WHERE clause is not requried, nor does it have any default value. The way the application using it was built allows users to create records but also doesn't insert any value. So there were a handful of nulls in there. The column was created but was never used in the database so when I created this query last week, I had gone back and updated all the existing records with '0'. I only care about tracking the new ones anyway. So when I looked at the data again, I realized what was going on.

This is why I hate working with data in databases I didn't create. You can't always account for things when you didn't know it needed to be accounted for in the first place.