Delete query doesn't!!

[Ron Le Blanc]

I have an Access 2003 database that has several tables in it that are all
related to a client table and have the cascade delete enabled.

I wish to delete all clients that have not been seen for two years or more.
A "meal" table contains the last visit date.

The idea is to find all clients who have not been seen in two or more years.
The data sheet view shows that the correct clients are selected. However,
when I try to run the delete query it says it cannot delete the records. The
"client" table is the table to which all other tables are related to and
have the cascade delete option on.

The SQL generated is:

DELETE client.*, client.MasterID, meals.LastVisit, client.f_name,
client.l_name
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)<Date()-730));

Does anyone have an idea as to why this does not work and what might be done
to make it work??

Thanks!

 

[[MVP] S.Clark]

Does the error msg say anything about primary key or unique index? If so,
add one and see if the problem goes away.

 

[Chris2]

I have an Access 2003 database that has several tables in it that are all
related to a client table and have the cascade delete enabled.

I wish to delete all clients that have not been seen for two years or more.
A "meal" table contains the last visit date.

The idea is to find all clients who have not been seen in two or more years.
The data sheet view shows that the correct clients are selected. However,
when I try to run the delete query it says it cannot delete the records. The
"client" table is the table to which all other tables are related to and
have the cascade delete option on.

The SQL generated is:

DELETE client.*, client.MasterID, meals.LastVisit, client.f_name,
client.l_name
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)<Date()-730));

Does anyone have an idea as to why this does not work and what might be done
to make it work??

Thanks!

Ron Le Blanc,

Please forgive the dates appended to the table names.

MasterID looks like the Primary Key of client, so I'm using it that
way.

CREATE TABLE client_10242005_1
(MasterID AUTOINCREMENT
,f_name TEXT(10)
,l_name TEXT(10)
,CONSTRAINT pk_client_10242005_1 PRIMARY KEY (MasterID)
)

CREATE TABLE meals_10242005_1
(mealsID AUTOINCREMENT
,MasterID LONG
,LastVisit DATETIME
,CONSTRAINT pk_meals_10242005_1 PRIMARY KEY (mealsID)
,CONSTRAINT fk_meals_10242005_1
FOREIGN KEY (MasterID)
REFERENCES client_10242005_1 (MasterID)
)

Open the relationships window and show both tables. Right-click on
the relationship link. Check both boxes for cascading on DELETES and
UPDATES.


SampleData

client_10242005_1
1, Jay, Smith
2, Dee, Smith
3, Ray, Smith


meals_10242005_1
1, 1, 01/01/2001
2, 1, 01/01/2002
3, 1, 01/01/2003
4, 2, 01/01/2001
5, 2, 01/01/2002
6, 2, 01/01/2004
7, 3, 01/01/2004
8, 3, 01/01/2005
9, 3, 01/10/2005

Expectations:

By dates, client 1 will need to be deleted.

The original query:

I've added table aliases to make my lengthy date-appended table names
more manageable.

(I got rid of "*" from the DELETE clause.)

DELETE C1.MasterID
,M1.LastVisit
,C1.f_name
,C1.l_name
FROM client_10242005_1 AS C1
INNER JOIN
meals_10242005_1 AS M1
ON C1.MasterID = M1.MasterID
WHERE (((m1.LastVisit)<Date()-730))

You're correct, the error message: "Could not delete from specified
table." was returned.


Well, let's try something else.

DELETE C1.*
FROM client_10242005_1 AS C1
WHERE C1.MasterID =
(SELECT DISTINCT
M1.MasterID
FROM meals_10242005_1 AS M1
WHERE M1.MasterID = C1.MasterID
AND (Date() - 730) > ALL
(SELECT M01.LastVisit
FROM meals_10242005_1 AS M01
WHERE M01.MasterID = C1.MasterID
)
)


This appears to have deleted Mr. Jay Smith, MasterID 1, from client.

Because we've got cascading deletes going, the meals table got it's
rows for Mr. Jay Smith deleted, as well.

Let me know what happens, and back up your data before trying it.


Sincerely,

Chris O.

 

[Van T. Dinh]

Not sure why you included Field names in the Delete clause.

Try simply:

DELETE client
FROM client INNER JOIN meals
ON client.MasterID = meals.MasterID
WHERE ((meals.LastVisit)<DateAdd("yyyy", -2, Date()));

 

[Van T. Dinh]

Sorry. The SQL should be:

DELETE client.*
FROM client INNER JOIN meals
ON client.MasterID = meals.MasterID
WHERE ((meals.LastVisit)<DateAdd("yyyy", -2, Date()));

 

[Ron Le Blanc]

This generates a "Cannot delete from specified table" error.

 

[Chris2]

Sorry. The SQL should be:

DELETE client.*
FROM client INNER JOIN meals
ON client.MasterID = meals.MasterID
WHERE ((meals.LastVisit)<DateAdd("yyyy", -2, Date()));

Van T. Dinh,

When I run the above query against my sample tables noted in my
earlier post, I receive the error: "Could not delete from specified
table."


Sincerely,

Chris O.

 

[Van T. Dinh]

OK. Try:

DELETE DISTINCT client.*
FROM client INNER JOIN meals
ON client.MasterID = meals.MasterID
WHERE ((meals.LastVisit)<DateAdd("yyyy", -2, Date()));

 

[Gary Walter]

PMFBI

A delete query involving more than
one table is one of the few situations
where *DISTINCTROW* means anything
anymore in Access 200x.

http://support.microsoft.com/default.aspx?scid=kb;en-us;207761

I might suggest trying on copy of data:

DELETE DISTINCTROW client.*
FROM client INNER JOIN meals
ON client.MasterID = meals.MasterID
WHERE
((meals.LastVisit)<DateAdd("yyyy", -2, Date()));

It has been awhile since I visited this...
so, if you don't mind, would you mind
posting back with your testing. The reason
I ask is because I vaguely remember
needing also a LEFT JOIN which does
not make sense to me right now.

Apologies again for butting in.

good luck,

gary

One other point is:

Are you sure you want to "lose data."

This can come back to bite you in
some situations.

A common alternative is to add a
field to "client" like "fActive" (default -1)
and instead of deleting, change "fActive"
to 0.

 

[John Spencer]

Pardon me for butting in. You probably have a relationship set between
Client and Meals. If you have it set to maintain data integrity, you cannot
delete the client until you have deleted ALL the related records in the
Meals table. Access will delete the client and the related meals records IF
you set Cascade Delete option to true.

From an earlier reply to your post entitled "Delete Query Problem"

Do you want to delete the records in the Meals table or in the Client table
or in both? You can only delete from one table at a time - unless you set
up a relationship with a cascade delete option

Does the meals table have multiple records for each MasterID? I am guessing
that LastVisit is a date field that actually specifies the date the meals
were received.

This query should delete all meal records for clients where the specific
client's most recent LastVisit date is more than 730 days ago.
DELETE DistinctRow meals.MasterID
FROM meals
WHERE Meals.MasterID in
(SELECT M.MasterID
FROM Meals as M
GROUP BY M.MasterID
HAVING Max(M.LastVisit) < Date()-730)

 

[John Spencer]

My apologies, I meant to post this to the OP not the responder. I hope
she/he will see this and respond.

 

[Gary Walter]

Good catch Chris!

I was so focused on
"Cannot delete from specified table" error,
I never paid attention to WHERE clause!

Does this make more sense?

DELETE DISTINCTROW client.*
FROM client
INNER JOIN
[SELECT
MasterID,
Max(LastVisit) As LatestVisit
FROM
meals
GROUP BY MasterID]. As m
ON
client.MasterID = m.MasterID
WHERE
((m.LastestVisit)<DateAdd("yyyy", -2, Date()));

or I suppose you could add
WHERE clause to virtual table.

Plus, John brought up something
I never thought about...

Depending on relationships
(and since you can only delete
from one table in query),
it is possible you cannot delete
from client as long as there are
records for those clients in meals...

I have to get back to my "job work"
and now regret tacking a what-I-thought
was a brief post to a more-complicated
situation than I have bandwidth to pursue.

Apologies and please disregard all
my posts to this thread.

gary

 

[Chris2]

OK. Try:

DELETE DISTINCT client.*
FROM client INNER JOIN meals
ON client.MasterID = meals.MasterID
WHERE ((meals.LastVisit)<DateAdd("yyyy", -2, Date()));

That generates:

"The Microsoft JET database engine does not recognize 'DISTINCT
client.*' as a valid field name or expression."


Sincerely,

Chris O.

 

[Chris2]

PMFBI

A delete query involving more than
one table is one of the few situations
where *DISTINCTROW* means anything
anymore in Access 200x.

http://support.microsoft.com/default.aspx?scid=kb;en-us;207761

I might suggest trying on copy of data:

DELETE DISTINCTROW client.*
FROM client INNER JOIN meals
ON client.MasterID = meals.MasterID
WHERE
((meals.LastVisit)<DateAdd("yyyy", -2, Date()));

It has been awhile since I visited this...
so, if you don't mind, would you mind
posting back with your testing. The reason
I ask is because I vaguely remember
needing also a LEFT JOIN which does
not make sense to me right now.

Apologies again for butting in.

good luck,

gary

One other point is:

Are you sure you want to "lose data."

This can come back to bite you in
some situations.

A common alternative is to add a
field to "client" like "fActive" (default -1)
and instead of deleting, change "fActive"
to 0.

Gary Walter,

The above query will attempt to delete clients that are still active.

When I run the above query, it attempts to delete Dee Smith (MasterID
2) from client, even though there is a LastVisit in meals more recent
that two years ago.


Sincerely,

Chris O.

 

[Chris2]

Pardon me for butting in. You probably have a relationship set between
Client and Meals.
Yes.


If you have it set to maintain data integrity, you cannot
delete the client until you have deleted ALL the related records in the
Meals table. Access will delete the client and the related meals records IF
you set Cascade Delete option to true.
Yes.


and:



From an earlier reply to your post entitled "Delete Query Problem"

Do you want to delete the records in the Meals table or in the Client table
or in both?



Does the meals table have multiple records for each MasterID? I am guessing
that LastVisit is a date field that actually specifies the date the meals
were received.

Only the OP can say for sure. I used multiple records in meals, but
that was an assumption on my part.


Sincerely,

Chris O.

 

[Ron Le Blanc]

Nope. It doesn't like the word "DISTINCT".

OK. Try:

DELETE DISTINCT client.*
FROM client INNER JOIN meals
ON client.MasterID = meals.MasterID
WHERE ((meals.LastVisit)<DateAdd("yyyy", -2, Date()));

 

[Van T. Dinh]

It looks like Access/JET doesn't like deleting the "One" Records in a Join.
Admiited, I mostly delete the "Many" Records first before deleting the "One"
Records whether I enforce Cascade Delete or not.

I think you may need a SubQuery in this case. Try something like:

DELETE client.*
FROM client
WHERE Client.MaterID Not In
(
SELECT DISTINCT Meals.MasterID
FROM Meals
WHERE (Meals.LastVisit > DateAdd("yyyy", -2, Date())
);

The above should also delete Records that don't have related Records in
Meals which is probably what you need. The only problem is this type of
Query is fairly slow because of the Not In Clause. Try also:

DELETE client.*
FROM client LEFT JOIN
(
SELECT Meals.MasterID
FROM Meals
WHERE (Meals.LastVisit > DateAdd("yyyy", -2, Date())
) AS SubQ
ON client.MasterID = SubQ.MasterID
WHERE SubQ.MasterID Is Null;

If the second SQL works (which I am not sure), it should me much faster than
the first SQL.

 

[Guest]

Chris2
I just applied similar syntax with "Distinctrow" and it appeared to delete
all required non-unique rows. Thanks for your input everyone.
Ben