Delete query with linked excel and access table

[JohnnyG23]

Ok, I have an Access table that is updated with changes from a linked Excel document. This table contains employment information so if there is a change in the system I will have a new employee table emailed and I will overwrite the old one. Now, what I need help with is the delete query. If an employee is terminated I need for the query to remove that employee's entire row from the CURRENT table. My predecessor started one but when I try and run it it just says is will delete 0 rows. Right now it should be deleting a little over 200 rows so I'm hoping someone can look at it and tell me any problems in it or guide me through creating a new one. Thanks.

DELETE DISTINCTROW CURRENT.*, Employees.TechID, CURRENT.TechID

FROM [CURRENT] INNER JOIN Employees ON CURRENT.TechID=Employees.TechID

WHERE (((Employees.TechID) Is Null) AND ((CURRENT.TechID) Not Between "1" And "100"));

 

[JohnnyG23]

I didn't see an edit button anywhere and I thought I would mention that a TechID consists of something like R01234567. They will always start with R and have eight numbers after that R. The TechID is also the primary key. Thanks.

 

[JohnnyG23]

Ok, I think I just figured it out. All I did was change INNER JOIN to LEFT JOIN and it removed the 200ish employees I needed. Thanks anyways though.

 

[GhostWolf]

The first thing I wonder about is the JOIN and WHERE statements: the JOIN matches the TechID fields, but the WHERE clause checks those fields for values that don't match.

How about something like

delete *
from CURRENT
where TechID not in (select TechID from Employees)

Or, since you receive a replacement for the CURRENT table via e-mail, you could run two SQL statements:

delete * from CURRENT;
select *
into CURRENT
from Employees;

 

[GhostWolf]

Aha. You finished typing before me, eh?!

Oh well, I can still use your original SQL to learn from.

 

[JohnnyG23]

I appreciate it though. I have never used a delete query so the syntax is very foreign to me, especially the WHERE and FROM parts of it. Does my correction work fine or would your code be a more proper method of doing it? Thanks.