Delete related data accross two tables.

[Pejayuk]

I have two tables linked by a one to many relationship
Table1
Record No (One)
CustomerDetails
DeliveryDate
Table2
Record No (Many)
OrderDetails
Quantity
I would like to delete everything in both tables where Table1.DeliveryDate = Today.
MsAccess keeps giving me a message "Could not delete from specified tables".

I can't seem to figure this one out.

Many thanks

 

[SiJP]

Do you have Cascade Delete Related Records checked in the relationships?

------------------------
Hit any User to continue

 

[PHV]

Something like this ?
DELETE FROM Table2 WHERE [Record No] IN
(SELECT [Record No] FROM Table1 WHERE DeliveryDate=Date());
And then :
DELETE FROM Table1 WHERE DeliveryDate=Date();

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

 

[Pejayuk]

PHV you star, that will work without the hassle of amending the relationships.
Many thanks all for your help

ö¿ô
\_/