Delete query (joined to 2 tables) won't delete Please help--dim bu

P

Paulymon

Trying to delete all records for an employee in the "Training Record"
database based on criteria from the "Employees" database. Both tables have
an "Employee ID" field to match them. I get a "Could not delete from
specified tables" message. Any idea why this won't won't work? And if it
just won't do it, any suggestions how to accomplish the same thing?

DELETE Employees.[Employee ID], [Training Record].*
FROM Employees INNER JOIN [Training Record] ON (Employees.[Employee ID] =
[Training Record].[Employee Id]) AND (Employees.[Employee ID] = [Training
Record].[Employee Id]) AND (Employees.[Employee ID] = [Training
Record].[Employee Id])
WHERE (((Employees.[Employee ID])="118474"));
 
V

Van T. Dinh

* The ON Clause looks incorrect. Why do you need to repeat the linking
Fields 3 times?

* Are [Employees].[Employee ID] and [Training Record].[Employee Id] numeric
Fields or Text Fields? If they are numeric Fields, your criteria is
incorrect since explicit numeric value should not be enclosed in " (which is
the delimiter for explicit Text / String values)

Try

DELETE [Training Record].*
FROM Employees WHERE ((([Training Record].[Employee Id])=118474));

* Lastly, avoid spaces and special characters in names. They only create
problems later if you are not careful.

HTH
Van T. Dinh
MVP (Access)
 
P

Paulymon

Thanks, the DistinctRows property was the trick.

Ofer said:
Try and change the UniqueRecords property of the query to true

Paulymon said:
Trying to delete all records for an employee in the "Training Record"
database based on criteria from the "Employees" database. Both tables have
an "Employee ID" field to match them. I get a "Could not delete from
specified tables" message. Any idea why this won't won't work? And if it
just won't do it, any suggestions how to accomplish the same thing?

DELETE Employees.[Employee ID], [Training Record].*
FROM Employees INNER JOIN [Training Record] ON (Employees.[Employee ID] =
[Training Record].[Employee Id]) AND (Employees.[Employee ID] = [Training
Record].[Employee Id]) AND (Employees.[Employee ID] = [Training
Record].[Employee Id])
WHERE (((Employees.[Employee ID])="118474"));
Click to expand...
Click to expand...
 
Top