Deleting Records from Two Tables

[instructorgirl]

I need to delete records from two joined tables with a one to many join based on an entered date range using a parameter. My SQL code follows:

DELETE tblCausalPart.*, tblWarrantyClaims.*, tblWarrantyClaims.[FAILURE DATE]
FROM tblCausalPart INNER JOIN tblWarrantyClaims ON tblCausalPart.[CAUSAL PART] = tblWarrantyClaims.[CAUSAL PART]
WHERE (((tblWarrantyClaims.[FAILURE DATE]) Between [Enter Beginning Date for Roll Off Month] And [Enter Ending Date for Roll Off Month]));

When I try to run the query I receive the following error message and nothing happens.

Could not delete from specified tables. (Error 3086)

Causal Part is the primary key in tblCausalPart and it is the foreign key in tblWarrantyClaims.

Is it possible to do this between two tables in Access? Any help would be greatly appreciated. Thanks!

 

[PHV]

You have to use 2 different delete queries, one for the child table (tblWarrantyClaims) and afterwards one for the master table (tblCausalPart).
Another way is to enforce referential integrity and cascading delete in the Relationship window.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244