Modifying Data in a Table

[ocular]

Have a table called Records. I use it as the data for a form that
represents each entry for a particular client.

The fields are IDRecord, Individual ID, Type, RecordDate, Notes,
Interviewer


The "Type" field can one of four - Letter,Phone, Consultation, Note
The "Individual ID" represents each client.

Each client can have many entries.

What I want to do is delete all the entries for a particular client if
the client has ever had an entry "Type" that = Consultation.

I want a query or SQL statement that will select all where Type =
"Consultation" and for all those Individual ID , I want all the records
(irrespective of the "Type") for those Individual ID's (clients) deleted
(removed from the table).

The abstract concept is straight forward but how do I do it with
Access97.

 

[hcj]

You could use a two-step process.
First create a Make Table query that collects Individual
ID for all "Consultation" instances in the original table.

Then, create a Delete query which joins the original and
the made table through Individual ID. Delete records
where Individual ID matches.

Hope this helps.

 

[John Vinson]

What I want to do is delete all the entries for a particular client if
the client has ever had an entry "Type" that = Consultation.

An subquery with an IN clause will do the trick here:

DELETE * FROM Records
WHERE IndividualID IN
(SELECT X.IndividualID FROM Records AS X
WHERE X.Type = "Consultation");

 

[ocular]

An subquery with an IN clause will do the trick here:

DELETE * FROM Records
WHERE IndividualID IN
(SELECT X.IndividualID FROM Records AS X
WHERE X.Type = "Consultation");

Thx John,

Deliciously simple and it works!