Delete from linked tables ms access

Question

I have a MS Access DB that has several tables.

My main table is called Client, which is linked to another table called Abonament, among others.

Both tables share the same field named client_id, which contain identical id's.

Table named Abonament has another field named data_expirarii which contains dates.

How do I delete all the entries from both tables (Client & Abonament) based on a specific date in data_expirarii field, from the Abonament table?

I've managed to delete them from the Abonament table using the Design Wizard and subsequently deleting every entry below or equal to 1-Jun-17 (<= 1-Jun-17) but the client entries remain in the Client table. MS Access gives me a headaque.

Answer 1

First you need to delete the records from the Abonament table.

DELETE  *
FROM    Abonament
WHERE   data_expirarii<=#06/01/2017 23:59:59#  

Note: the US format of the date (mm/dd/yyyy)- this format must be used.

Next any client records that no longer have any matching records in Abonament can be deleted. This query will return all records from Clients (written on the LEFT of the join) and only those records in Abonament that have a matching Client_ID - any other record will return NULL in the Abonament.Client_ID field which are filtered for in the WHERE clause and deleted.

DELETE DISTINCTROW Clients.*
FROM    Clients LEFT JOIN Abonament ON Clients.Client_ID = Abonament.Client_ID
WHERE   Abonament.Client_ID IS NULL  

Make sure to take a back-up of your data while testing!