What's Wrong With This MS Access Query?

AGx-07_162

Honorable
Sep 16, 2013
58
0
10,610
5
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?
 

AGx-07_162

Honorable
Sep 16, 2013
58
0
10,610
5


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

Honorable
Sep 16, 2013
58
0
10,610
5
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.
 
Thread starter Similar threads Forum Replies Date
A Apps General Discussion 1
O Apps General Discussion 6
A Apps General Discussion 2
Z Apps General Discussion 8
D Apps General Discussion 5
L Apps General Discussion 1
B Apps General Discussion 3
A Apps General Discussion 2
A Apps General Discussion 13
B Apps General Discussion 2
A Apps General Discussion 5
D Apps General Discussion 1
S Apps General Discussion 2
V Apps General Discussion 1
S Apps General Discussion 4
L Apps General Discussion 8
A Apps General Discussion 1
G Apps General Discussion 3
H Apps General Discussion 10
Wing0 Apps General Discussion 8

ASK THE COMMUNITY