Delete query

[Dianne Groom]

I have an Access database with two tables, a Client Details table with
Client ID as the key field, and a Client Visits table with Record ID, Client
ID and Visit Date fields.

I have created a Most Recent Visit Date query, with fields Client ID (Group
by) and Visit Date (Max).

I have tried to create a Delete query to delete all records for clients in a
specified date range.

I used * (all fields) from the Client Details table as the "From" field and
MaxOfVisit Date from the Most Recent Visit Date query as the "Where" field ,
specifying a date range as the criterion.

When I run the delete query the following message is displayed "Could not
delete from specified tables".

Your help would be appreciated.

 

[John W. Vinson]

I used * (all fields) from the Client Details table as the "From" field and
MaxOfVisit Date from the Most Recent Visit Date query as the "Where" field ,
specifying a date range as the criterion.

When I run the delete query the following message is displayed "Could not
delete from specified tables".

Please open the query in SQL view (use the View menu item in query design
view, or the leftmost tool in the toolbar) and copy and paste the SQL to a
message here.

Have you defined a Relationship between Clients and ClientDetails? That may be
the issue.

John W. Vinson [MVP]

 

[Guest]

By joining the table to to an aggregating query (your Most Recent Visit Date
query) the resulting query won't be updatable. You can do it with a
subquery, however. For all clients with a latest visit date in 2006 for
instance either of these should do it:

DELETE *
FROM [Client Details]
WHERE EXISTS
(SELECT [Client ID]
FROM [Client Visits]
WHERE [Client Visits].[Client ID] = [Client Details].[Client ID]
GROUP BY [Client ID]
HAVING MAX([Visit Date])
BETWEEN #01/01/2006# AND #12/31/2006#);

DELETE *
FROM [Client Details]
WHERE
(SELECT MAX([Visit Date])
FROM [Client Visits]
WHERE [Client Visits].[Client ID] = [Client Details].[Client ID])
BETWEEN #01/01/2006# AND #12/31/2006#;

You should enforce cascade deletions in the relationship between the tables
so that all matching rows from the ClientVisits table are also deleted.
Before undertaking operations which effect a set of rows like this you should
ensure that the data is backed up – just in case!

Ken Sheridan
Stafford, England

 

[Dianne Groom]

Thank you Ken - it worked well. I now know how to do a subquery.
Dianne

By joining the table to to an aggregating query (your Most Recent Visit
Date
query) the resulting query won't be updatable. You can do it with a
subquery, however. For all clients with a latest visit date in 2006 for
instance either of these should do it:

DELETE *
FROM [Client Details]
WHERE EXISTS
(SELECT [Client ID]
FROM [Client Visits]
WHERE [Client Visits].[Client ID] = [Client Details].[Client ID]
GROUP BY [Client ID]
HAVING MAX([Visit Date])
BETWEEN #01/01/2006# AND #12/31/2006#);

DELETE *
FROM [Client Details]
WHERE
(SELECT MAX([Visit Date])
FROM [Client Visits]
WHERE [Client Visits].[Client ID] = [Client Details].[Client ID])
BETWEEN #01/01/2006# AND #12/31/2006#;

You should enforce cascade deletions in the relationship between the
tables
so that all matching rows from the ClientVisits table are also deleted.
Before undertaking operations which effect a set of rows like this you
should
ensure that the data is backed up - just in case!

Ken Sheridan
Stafford, England

I have an Access database with two tables, a Client Details table with
Client ID as the key field, and a Client Visits table with Record ID,
Client
ID and Visit Date fields.

I have created a Most Recent Visit Date query, with fields Client ID
(Group
by) and Visit Date (Max).

I have tried to create a Delete query to delete all records for clients
in a
specified date range.

I used * (all fields) from the Client Details table as the "From" field
and
MaxOfVisit Date from the Most Recent Visit Date query as the "Where"
field ,
specifying a date range as the criterion.

When I run the delete query the following message is displayed "Could not
delete from specified tables".

Your help would be appreciated.

 

[Jamie Collins]

I have tried to create a Delete query to delete all records for clients in a
specified date range.

I used * (all fields) from the Client Details table as the "From" field

DELETE *
FROM [Client Details]
<<snipped>>

The idea of DELETE * FROM ... (illegal in standard SQL) has always
seemed non-intuitive to me: does it remove all columns <g>?! I've
never understood how it came about: could the query builder tool thing
be to blame by requiring the user to specify at least one field?

<<snipped>>
WHERE
(SELECT MAX([VisitDate])
FROM [Client Visits]
WHERE [Client Visits].[Client ID] = [Client Details].[Client ID])
BETWEEN #01/01/2006# AND #12/31/2006#;

The above would omit #2006-12-31 00:00:01#, #2006-12-31 00:00:02#,
#2006-12-31 00:00:03# and 86,396 other distinct DATETIME values which
fall on that day

I suggest you use

BETWEEN #2006-01-01 00:00:00# AND #2006-12-31 23:59:59#;

Jamie.

--