Delete Query Help

[ptrifile]

Looking for some help. I have a delete query that does not appear to be working. I have two tables (tblCurrentTimeOff and tblEmployee). When the query runs, if tblCurrentTimeOff.EmployeeNumber matches an EmployeeNumber in tblemployee I would like to delete that record in tblEmployee.

I thought this was working before but it appears not to be. Here is the SQL that I am using:

DELETE DISTINCTROW tblemployee.*
FROM tblemployee INNER JOIN tblCurrentTimeOffTuesday ON tblemployee.EmployeeNumber = tblCurrentTimeOffTuesday.EmployeeNumber;

Can anyone help me get this working? Much appreciated!

Paul

 

[dhookom]

What happens if you remove DISTINCTROW?

I typically don't delete records. I have an "Active" field that I set to 0.

Duane
Hook'D on Access
MS Access MVP

 

[ptrifile]

Thanks for the quick response dhookom. When I remove DISTINCTROW i get an error "could not delete from specified tables". I think i have an issue somewhere else as it appears as if sometimes it works and sometimes it doesn't. I need to try and nail down whats happening in both circumstances. Thanks for the help.

 

[dhookom]

I don't understand the logic of deleting an employee record however, you might try:

DELETE tblemployee.*
FROM tblemployee 
WHERE EmployeeNumber IN (SELECT EmployeeNumber FROM tblCurrentTimeOffTuesday);

Duane
Hook'D on Access
MS Access MVP