Delete Query Does Not

[Ron Le Blanc]

I have a database that won't let me delete records using a Delete Query.

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

I wish to delete all clients that have not been seen for two years or more.
And yes, I really want to delete them.

A "meal" table contains the last visit date. See relationships...

The idea is to find all clients who have not been seen in two or more years
and delete them.
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.

I don't care if I loose all the data for a client. If the client has not
been in in two years a new record would have to be created anyway.

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));

The error message says: "Cannot delete from table".

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

Thanks!

 

[John Vinson]

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));

The error message says: "Cannot delete from table".

You're trying too hard. Don't delete anything from the Meals table -
cascading deletes will take care of that.

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


John W. Vinson[MVP]

 

[John Spencer]

John,

You've made the assumption that cascading delete has been implemented.

Ron,
If you have not set up the relationships and implemented cascading deletes when
you did so, then you will have to run two queries.

Delete the appropriate records in the meal table
Then delete records in the client table that don't have any record(s) in the
meal table.

 

[Tom Ellison]

And, if you have not implemented cascading deletes, you will know this when
you try to delete the client row without deleting the meal rows first.
You'll get a message about this, but the data will not be damaged, as the
delete will simply fail. At that point, either implement the cascading or
use Mr. Spencer's suggestion.

You should check the meal table to make sure this has worked. Have the rows
for the related client row been deleted, or are they still there, orphaned?
If they have not been deleted, check the client table to confirm they are
truly orphaned. If so, then the relationship has not been created to
prevent this.

Thorough testing and knowing what to do about it is essential before going
production on a thing like this.

Tom Ellison

 

[John Vinson]

You've made the assumption that cascading delete has been implemented.

Ron did say:

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


John W. Vinson[MVP]

 

[Ron Le Blanc]

This does not work. It selects many clients who have been in within two
years.
It does find a bunch of the "right" clients to delete but also includes what
seems be be random other clients not meeting the two years since last time
they came in.

 

[Tom Ellison]

Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your meaning of
"LastVisit."

If you have several rows in [meals] each with a different LastVisit, it is
possible you want to delete the client and all meals only when the most
recent LastVisit is prior to 730 days ago. Is that what you want?

The query as I read it would delete a client and related meals if there is
ANY visit more than 730 days ago. That would be a quite different thing.
It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what rows
would be deleted if it were changed to be a DELETE query. By not deleting
anything, testing is easier, as you can change it and run it again without
having to restore the data every time it deletes wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your system
date is off, you could be removing rows you do not intend. That sounds like
a bit of a risk to me.

Tom Ellison

 

[John Spencer]

Ouch. I completely missed that. Should have known better than to correct the wizard.

 

[Ron Le Blanc]

The meals table contains the client master ID, a meals master ID, a list of
date, one per row, of the dates the client has visited the food bank. There
can be and often are multiple entries per client. The last row contains the
last date a client came into the food bank. Soooo... if that date is two or
more years old I want to delete the client and all records related to this
client.

Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your meaning
of "LastVisit."

If you have several rows in [meals] each with a different LastVisit, it is
possible you want to delete the client and all meals only when the most
recent LastVisit is prior to 730 days ago. Is that what you want?

The query as I read it would delete a client and related meals if there is
ANY visit more than 730 days ago. That would be a quite different thing.
It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what rows
would be deleted if it were changed to be a DELETE query. By not deleting
anything, testing is easier, as you can change it and run it again without
having to restore the data every time it deletes wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your system
date is off, you could be removing rows you do not intend. That sounds
like a bit of a risk to me.

Tom Ellison

This does not work. It selects many clients who have been in within two
years.
It does find a bunch of the "right" clients to delete but also includes
what seems be be random other clients not meeting the two years since
last time they came in.

 

[Tom Ellison]

Dear Ron:

Then it sounds like my understanding is correct. Have you tried what I
proposed?

Tom Ellison

The meals table contains the client master ID, a meals master ID, a list
of date, one per row, of the dates the client has visited the food bank.
There can be and often are multiple entries per client. The last row
contains the last date a client came into the food bank. Soooo... if that
date is two or more years old I want to delete the client and all records
related to this client.

Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your meaning
of "LastVisit."

If you have several rows in [meals] each with a different LastVisit, it
is possible you want to delete the client and all meals only when the
most recent LastVisit is prior to 730 days ago. Is that what you want?

The query as I read it would delete a client and related meals if there
is ANY visit more than 730 days ago. That would be a quite different
thing. It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what rows
would be deleted if it were changed to be a DELETE query. By not
deleting anything, testing is easier, as you can change it and run it
again without having to restore the data every time it deletes wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your
system date is off, you could be removing rows you do not intend. That
sounds like a bit of a risk to me.

Tom Ellison

This does not work. It selects many clients who have been in within two
years.
It does find a bunch of the "right" clients to delete but also includes
what seems be be random other clients not meeting the two years since
last time they came in.




On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"

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));

The error message says: "Cannot delete from table".

You're trying too hard. Don't delete anything from the Meals table -
cascading deletes will take care of that.

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


John W. Vinson[MVP]

 

[Ron Le Blanc]

The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in (MAX(meals.LastVisit)<Date()-730

The meals table contains the client master ID, a meals master ID, (those two
create the combined key for the meals table), a list of date, one per row,
of the dates the client has visited the food bank. There
can be and often are multiple entries per client. The last row contains the
last date a client came into the food bank. Soooo... if that date is two or
more years old I want to delete the client and all records related to this
client.

Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your meaning
of "LastVisit."

If you have several rows in [meals] each with a different LastVisit, it is
possible you want to delete the client and all meals only when the most
recent LastVisit is prior to 730 days ago. Is that what you want?

The query as I read it would delete a client and related meals if there is
ANY visit more than 730 days ago. That would be a quite different thing.
It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what rows
would be deleted if it were changed to be a DELETE query. By not deleting
anything, testing is easier, as you can change it and run it again without
having to restore the data every time it deletes wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your system
date is off, you could be removing rows you do not intend. That sounds
like a bit of a risk to me.

Tom Ellison

This does not work. It selects many clients who have been in within two
years.
It does find a bunch of the "right" clients to delete but also includes
what seems be be random other clients not meeting the two years since
last time they came in.

 

[Tom Ellison]

Dear Ron:

Maybe I should make excuses. Not.

OK, this may be what is required:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

Funny what you forget at times. I THINK this is what is needed. Please get
back, OK? This is sanity check day for me!

Tom Ellison

The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)<Date()-730

The meals table contains the client master ID, a meals master ID, (those
two create the combined key for the meals table), a list of date, one per
row, of the dates the client has visited the food bank. There
can be and often are multiple entries per client. The last row contains
the
last date a client came into the food bank. Soooo... if that date is two
or
more years old I want to delete the client and all records related to
this
client.

Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your meaning
of "LastVisit."

If you have several rows in [meals] each with a different LastVisit, it
is possible you want to delete the client and all meals only when the
most recent LastVisit is prior to 730 days ago. Is that what you want?

The query as I read it would delete a client and related meals if there
is ANY visit more than 730 days ago. That would be a quite different
thing. It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what rows
would be deleted if it were changed to be a DELETE query. By not
deleting anything, testing is easier, as you can change it and run it
again without having to restore the data every time it deletes wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your
system date is off, you could be removing rows you do not intend. That
sounds like a bit of a risk to me.

Tom Ellison

This does not work. It selects many clients who have been in within two
years.
It does find a bunch of the "right" clients to delete but also includes
what seems be be random other clients not meeting the two years since
last time they came in.




On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"

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));

The error message says: "Cannot delete from table".

You're trying too hard. Don't delete anything from the Meals table -
cascading deletes will take care of that.

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


John W. Vinson[MVP]

 

[Ron Le Blanc]

Dear Tom,

Syntax error in HAVING clause

<Sigh>

Take care,

Dear Ron:

Maybe I should make excuses. Not.

OK, this may be what is required:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

Funny what you forget at times. I THINK this is what is needed. Please
get back, OK? This is sanity check day for me!

Tom Ellison

The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)<Date()-730

The meals table contains the client master ID, a meals master ID, (those
two create the combined key for the meals table), a list of date, one per
row, of the dates the client has visited the food bank. There
can be and often are multiple entries per client. The last row contains
the
last date a client came into the food bank. Soooo... if that date is two
or
more years old I want to delete the client and all records related to
this
client.

Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your meaning
of "LastVisit."

If you have several rows in [meals] each with a different LastVisit, it
is possible you want to delete the client and all meals only when the
most recent LastVisit is prior to 730 days ago. Is that what you want?

The query as I read it would delete a client and related meals if there
is ANY visit more than 730 days ago. That would be a quite different
thing. It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what
rows would be deleted if it were changed to be a DELETE query. By not
deleting anything, testing is easier, as you can change it and run it
again without having to restore the data every time it deletes wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your
system date is off, you could be removing rows you do not intend. That
sounds like a bit of a risk to me.

Tom Ellison


This does not work. It selects many clients who have been in within two
years.
It does find a bunch of the "right" clients to delete but also includes
what seems be be random other clients not meeting the two years since
last time they came in.




On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"

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));

The error message says: "Cannot delete from table".

You're trying too hard. Don't delete anything from the Meals table -
cascading deletes will take care of that.

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


John W. Vinson[MVP]

 

[Tom Ellison]

Dear Ron:

Well, I used to be able to write queries. Actually, it's easy enough when
you have the database in front of you.

I think I had the order wrong, didn't I?

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730

I've been on my own problem almost straight for 48 hours. Please forgive.
I'm too tired, or old, or something.

Tom Ellison

Dear Tom,

Syntax error in HAVING clause

<Sigh>

Take care,

Dear Ron:

Maybe I should make excuses. Not.

OK, this may be what is required:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

Funny what you forget at times. I THINK this is what is needed. Please
get back, OK? This is sanity check day for me!

Tom Ellison

The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)<Date()-730

The meals table contains the client master ID, a meals master ID, (those
two create the combined key for the meals table), a list of date, one
per row, of the dates the client has visited the food bank. There
can be and often are multiple entries per client. The last row contains
the
last date a client came into the food bank. Soooo... if that date is
two or
more years old I want to delete the client and all records related to
this
client.




Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your
meaning of "LastVisit."

If you have several rows in [meals] each with a different LastVisit, it
is possible you want to delete the client and all meals only when the
most recent LastVisit is prior to 730 days ago. Is that what you want?

The query as I read it would delete a client and related meals if there
is ANY visit more than 730 days ago. That would be a quite different
thing. It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what
rows would be deleted if it were changed to be a DELETE query. By not
deleting anything, testing is easier, as you can change it and run it
again without having to restore the data every time it deletes wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your
system date is off, you could be removing rows you do not intend. That
sounds like a bit of a risk to me.

Tom Ellison


This does not work. It selects many clients who have been in within
two years.
It does find a bunch of the "right" clients to delete but also
includes what seems be be random other clients not meeting the two
years since last time they came in.




On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"

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));

The error message says: "Cannot delete from table".

You're trying too hard. Don't delete anything from the Meals table -
cascading deletes will take care of that.

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


John W. Vinson[MVP]

 

[Ron Le Blanc]

Dear Tom,

First I thank you for all the help you have given me thus far. This is a
nasty problem!

The last SQL query you gave me (below) runs but gives the following error
message:

"Specify the table containing the records you want to delete"

If I make it a Select query it selects the correct clients!! But a Delete
query give the above error message.

One more tiny tweak maybe?

Thanks and take care...

Dear Ron:

Well, I used to be able to write queries. Actually, it's easy enough when
you have the database in front of you.

I think I had the order wrong, didn't I?

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730

I've been on my own problem almost straight for 48 hours. Please forgive.
I'm too tired, or old, or something.

Tom Ellison

Dear Tom,

Syntax error in HAVING clause

<Sigh>

Take care,

Dear Ron:

Maybe I should make excuses. Not.

OK, this may be what is required:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

Funny what you forget at times. I THINK this is what is needed. Please
get back, OK? This is sanity check day for me!

Tom Ellison


The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)<Date()-730

The meals table contains the client master ID, a meals master ID,
(those two create the combined key for the meals table), a list of
date, one per row, of the dates the client has visited the food bank.
There
can be and often are multiple entries per client. The last row contains
the
last date a client came into the food bank. Soooo... if that date is
two or
more years old I want to delete the client and all records related to
this
client.




Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your
meaning of "LastVisit."

If you have several rows in [meals] each with a different LastVisit,
it is possible you want to delete the client and all meals only when
the most recent LastVisit is prior to 730 days ago. Is that what you
want?

The query as I read it would delete a client and related meals if
there is ANY visit more than 730 days ago. That would be a quite
different thing. It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what
rows would be deleted if it were changed to be a DELETE query. By not
deleting anything, testing is easier, as you can change it and run it
again without having to restore the data every time it deletes
wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your
system date is off, you could be removing rows you do not intend.
That sounds like a bit of a risk to me.

Tom Ellison


This does not work. It selects many clients who have been in within
two years.
It does find a bunch of the "right" clients to delete but also
includes what seems be be random other clients not meeting the two
years since last time they came in.




On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"

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));

The error message says: "Cannot delete from table".

You're trying too hard. Don't delete anything from the Meals table -
cascading deletes will take care of that.

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


John W. Vinson[MVP]

 

[Tom Ellison]

Dear Ron:

It's been years since I did this, but it seems to me that, if you take the
working SELECT query, open it in design view, right click in the empty
portion of the top portion of the windos, select Query Type, and then select
Delete Query, it will transform your SELECT query into an equivalent DELETE
query.

I beleive what it would say is:

DELETE client.*
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730

I'm not sure of this query either, but I'm pretty sure using the Design View
capacities to create it will be reliable. Of course, always make a backup
of your data before running anything I suggest. : )

Or anything else new and uncertain you are just testing for the first time.
Or the second time.

Did I mention before that I'm concerned about the use of Date() in this
query. If you system clock is somehow accidentally set forward to 2/9/2008,
it will delete all your data. What is deleted here depends on the system
clock being set correctly. Having the user enter the desired date might be
better, but that too is subject to mis-typing. The best thing I can think
of at the moment is to rank your data by date and choose a date from that
which assures you the most recent 200, 500, 10000, or whatever number of
records are most recent will not be deleted. Test the date for your
deletions against that. It must be a date prior to your 500 most recent
records.

A good question might be to consider for yourself why it is so necessary to
delete them at all. You could use a similar query mechanism to just
disregard them for reporting or forms purposes, and let them be. This
avoids the possible tragic loss of historic information, especially if your
backup scheme is not highly reliable. If at some future time you want to
change the number of days from 730 to what seems better at that time,
everything would adjust to the new limit immediately. If you set it to 1000
days, you'd instantly have 270 days more information show up (assuming there
is some in your table). If table size (the 2 GB max for Jet files, for
example) or performance is your goal, then archiving them first to a
separate database would be an option. Ask yourself, if all the data you
have accumulated in this table were to disappear due to such a malfunction,
what amount of time and money would it cost? Now are you motivated to
protect it thoroughly?

Well, I've given you the rope to hang yourself. : ) What you do with it
is up to you!

Tom Ellison

Dear Tom,

First I thank you for all the help you have given me thus far. This is
a nasty problem!

The last SQL query you gave me (below) runs but gives the following error
message:

"Specify the table containing the records you want to delete"

If I make it a Select query it selects the correct clients!! But a Delete
query give the above error message.

One more tiny tweak maybe?

Thanks and take care...

Dear Ron:

Well, I used to be able to write queries. Actually, it's easy enough
when you have the database in front of you.

I think I had the order wrong, didn't I?

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730

I've been on my own problem almost straight for 48 hours. Please
forgive. I'm too tired, or old, or something.

Tom Ellison

Dear Tom,

Syntax error in HAVING clause

<Sigh>

Take care,

Dear Ron:

Maybe I should make excuses. Not.

OK, this may be what is required:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

Funny what you forget at times. I THINK this is what is needed.
Please get back, OK? This is sanity check day for me!

Tom Ellison


The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)<Date()-730

The meals table contains the client master ID, a meals master ID,
(those two create the combined key for the meals table), a list of
date, one per row, of the dates the client has visited the food bank.
There
can be and often are multiple entries per client. The last row
contains the
last date a client came into the food bank. Soooo... if that date is
two or
more years old I want to delete the client and all records related to
this
client.




Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your
meaning of "LastVisit."

If you have several rows in [meals] each with a different LastVisit,
it is possible you want to delete the client and all meals only when
the most recent LastVisit is prior to 730 days ago. Is that what you
want?

The query as I read it would delete a client and related meals if
there is ANY visit more than 730 days ago. That would be a quite
different thing. It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what
rows would be deleted if it were changed to be a DELETE query. By
not deleting anything, testing is easier, as you can change it and
run it again without having to restore the data every time it deletes
wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your
system date is off, you could be removing rows you do not intend.
That sounds like a bit of a risk to me.

Tom Ellison


This does not work. It selects many clients who have been in within
two years.
It does find a bunch of the "right" clients to delete but also
includes what seems be be random other clients not meeting the two
years since last time they came in.




On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"

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));

The error message says: "Cannot delete from table".

You're trying too hard. Don't delete anything from the Meals
table -
cascading deletes will take care of that.

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


John W. Vinson[MVP]

 

[Tom Ellison]

Now, in a spelling bee, I would never spell it "beleive", but when I let my
fingers to the walking (TM) strange things happen. So, please, make that
"believe" OK?

Tom Ellison

Dear Ron:

It's been years since I did this, but it seems to me that, if you take the
working SELECT query, open it in design view, right click in the empty
portion of the top portion of the windos, select Query Type, and then
select Delete Query, it will transform your SELECT query into an
equivalent DELETE query.

I beleive what it would say is:

DELETE client.*
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730

I'm not sure of this query either, but I'm pretty sure using the Design
View capacities to create it will be reliable. Of course, always make a
backup of your data before running anything I suggest. : )

Or anything else new and uncertain you are just testing for the first
time. Or the second time.

Did I mention before that I'm concerned about the use of Date() in this
query. If you system clock is somehow accidentally set forward to
2/9/2008, it will delete all your data. What is deleted here depends on
the system clock being set correctly. Having the user enter the desired
date might be better, but that too is subject to mis-typing. The best
thing I can think of at the moment is to rank your data by date and choose
a date from that which assures you the most recent 200, 500, 10000, or
whatever number of records are most recent will not be deleted. Test the
date for your deletions against that. It must be a date prior to your 500
most recent records.

A good question might be to consider for yourself why it is so necessary
to delete them at all. You could use a similar query mechanism to just
disregard them for reporting or forms purposes, and let them be. This
avoids the possible tragic loss of historic information, especially if
your backup scheme is not highly reliable. If at some future time you
want to change the number of days from 730 to what seems better at that
time, everything would adjust to the new limit immediately. If you set it
to 1000 days, you'd instantly have 270 days more information show up
(assuming there is some in your table). If table size (the 2 GB max for
Jet files, for example) or performance is your goal, then archiving them
first to a separate database would be an option. Ask yourself, if all the
data you have accumulated in this table were to disappear due to such a
malfunction, what amount of time and money would it cost? Now are you
motivated to protect it thoroughly?

Well, I've given you the rope to hang yourself. : ) What you do with it
is up to you!

Tom Ellison

Dear Tom,

First I thank you for all the help you have given me thus far. This is
a nasty problem!

The last SQL query you gave me (below) runs but gives the following error
message:

"Specify the table containing the records you want to delete"

If I make it a Select query it selects the correct clients!! But a Delete
query give the above error message.

One more tiny tweak maybe?

Thanks and take care...

Dear Ron:

Well, I used to be able to write queries. Actually, it's easy enough
when you have the database in front of you.

I think I had the order wrong, didn't I?

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730

I've been on my own problem almost straight for 48 hours. Please
forgive. I'm too tired, or old, or something.

Tom Ellison


Dear Tom,

Syntax error in HAVING clause

<Sigh>

Take care,

Dear Ron:

Maybe I should make excuses. Not.

OK, this may be what is required:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

Funny what you forget at times. I THINK this is what is needed.
Please get back, OK? This is sanity check day for me!

Tom Ellison


The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)<Date()-730

The meals table contains the client master ID, a meals master ID,
(those two create the combined key for the meals table), a list of
date, one per row, of the dates the client has visited the food bank.
There
can be and often are multiple entries per client. The last row
contains the
last date a client came into the food bank. Soooo... if that date is
two or
more years old I want to delete the client and all records related
to this
client.




Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your
meaning of "LastVisit."

If you have several rows in [meals] each with a different LastVisit,
it is possible you want to delete the client and all meals only when
the most recent LastVisit is prior to 730 days ago. Is that what
you want?

The query as I read it would delete a client and related meals if
there is ANY visit more than 730 days ago. That would be a quite
different thing. It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what
rows would be deleted if it were changed to be a DELETE query. By
not deleting anything, testing is easier, as you can change it and
run it again without having to restore the data every time it
deletes wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your
system date is off, you could be removing rows you do not intend.
That sounds like a bit of a risk to me.

Tom Ellison


This does not work. It selects many clients who have been in within
two years.
It does find a bunch of the "right" clients to delete but also
includes what seems be be random other clients not meeting the two
years since last time they came in.




On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"

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));

The error message says: "Cannot delete from table".

You're trying too hard. Don't delete anything from the Meals
table -
cascading deletes will take care of that.

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


John W. Vinson[MVP]

 

[Ron Le Blanc]

<Sigh>

Dear Tom,

Almost but no cigar. When I try to run the SQL query below it gives me the
following error:

Cannot group on fields selected with '*' (client)

If it would be easier I could zip up the files and send them to you. Ah,
only thing is you would have to agree that you never "look" at the actual
data as these are real Food Bank Clients who expect there data to be held
privately. Or I could send you the relationship diagrams, or both. Whatever
makes it easier for you.

Thanks for all your help!!
Take care,

Dear Ron:

It's been years since I did this, but it seems to me that, if you take the
working SELECT query, open it in design view, right click in the empty
portion of the top portion of the windos, select Query Type, and then
select Delete Query, it will transform your SELECT query into an
equivalent DELETE query.

I beleive what it would say is:

DELETE client.*
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730

I'm not sure of this query either, but I'm pretty sure using the Design
View capacities to create it will be reliable. Of course, always make a
backup of your data before running anything I suggest. : )

Or anything else new and uncertain you are just testing for the first
time. Or the second time.

Did I mention before that I'm concerned about the use of Date() in this
query. If you system clock is somehow accidentally set forward to
2/9/2008, it will delete all your data. What is deleted here depends on
the system clock being set correctly. Having the user enter the desired
date might be better, but that too is subject to mis-typing. The best
thing I can think of at the moment is to rank your data by date and choose
a date from that which assures you the most recent 200, 500, 10000, or
whatever number of records are most recent will not be deleted. Test the
date for your deletions against that. It must be a date prior to your 500
most recent records.

A good question might be to consider for yourself why it is so necessary
to delete them at all. You could use a similar query mechanism to just
disregard them for reporting or forms purposes, and let them be. This
avoids the possible tragic loss of historic information, especially if
your backup scheme is not highly reliable. If at some future time you
want to change the number of days from 730 to what seems better at that
time, everything would adjust to the new limit immediately. If you set it
to 1000 days, you'd instantly have 270 days more information show up
(assuming there is some in your table). If table size (the 2 GB max for
Jet files, for example) or performance is your goal, then archiving them
first to a separate database would be an option. Ask yourself, if all the
data you have accumulated in this table were to disappear due to such a
malfunction, what amount of time and money would it cost? Now are you
motivated to protect it thoroughly?

Well, I've given you the rope to hang yourself. : ) What you do with it
is up to you!

Tom Ellison

Dear Tom,

First I thank you for all the help you have given me thus far. This is
a nasty problem!

The last SQL query you gave me (below) runs but gives the following error
message:

"Specify the table containing the records you want to delete"

If I make it a Select query it selects the correct clients!! But a Delete
query give the above error message.

One more tiny tweak maybe?

Thanks and take care...

Dear Ron:

Well, I used to be able to write queries. Actually, it's easy enough
when you have the database in front of you.

I think I had the order wrong, didn't I?

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730

I've been on my own problem almost straight for 48 hours. Please
forgive. I'm too tired, or old, or something.

Tom Ellison


Dear Tom,

Syntax error in HAVING clause

<Sigh>

Take care,

Dear Ron:

Maybe I should make excuses. Not.

OK, this may be what is required:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

Funny what you forget at times. I THINK this is what is needed.
Please get back, OK? This is sanity check day for me!

Tom Ellison


The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)<Date()-730

The meals table contains the client master ID, a meals master ID,
(those two create the combined key for the meals table), a list of
date, one per row, of the dates the client has visited the food bank.
There
can be and often are multiple entries per client. The last row
contains the
last date a client came into the food bank. Soooo... if that date is
two or
more years old I want to delete the client and all records related
to this
client.




Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your
meaning of "LastVisit."

If you have several rows in [meals] each with a different LastVisit,
it is possible you want to delete the client and all meals only when
the most recent LastVisit is prior to 730 days ago. Is that what
you want?

The query as I read it would delete a client and related meals if
there is ANY visit more than 730 days ago. That would be a quite
different thing. It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what
rows would be deleted if it were changed to be a DELETE query. By
not deleting anything, testing is easier, as you can change it and
run it again without having to restore the data every time it
deletes wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your
system date is off, you could be removing rows you do not intend.
That sounds like a bit of a risk to me.

Tom Ellison


This does not work. It selects many clients who have been in within
two years.
It does find a bunch of the "right" clients to delete but also
includes what seems be be random other clients not meeting the two
years since last time they came in.




On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"

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));

The error message says: "Cannot delete from table".

You're trying too hard. Don't delete anything from the Meals
table -
cascading deletes will take care of that.

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


John W. Vinson[MVP]

 

[Tom Ellison]

Dear Ron:

Did you try the method of changing the query in the Design View, as I
suggested?

Or does this fix it:

DELETE client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730

Sorry I missed that.

Tom Ellison

<Sigh>

Dear Tom,

Almost but no cigar. When I try to run the SQL query below it gives me the
following error:

Cannot group on fields selected with '*' (client)

If it would be easier I could zip up the files and send them to you. Ah,
only thing is you would have to agree that you never "look" at the actual
data as these are real Food Bank Clients who expect there data to be held
privately. Or I could send you the relationship diagrams, or both.
Whatever makes it easier for you.

Thanks for all your help!!
Take care,

Dear Ron:

It's been years since I did this, but it seems to me that, if you take
the working SELECT query, open it in design view, right click in the
empty portion of the top portion of the windos, select Query Type, and
then select Delete Query, it will transform your SELECT query into an
equivalent DELETE query.

I beleive what it would say is:

DELETE client.*
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730

I'm not sure of this query either, but I'm pretty sure using the Design
View capacities to create it will be reliable. Of course, always make a
backup of your data before running anything I suggest. : )

Or anything else new and uncertain you are just testing for the first
time. Or the second time.

Did I mention before that I'm concerned about the use of Date() in this
query. If you system clock is somehow accidentally set forward to
2/9/2008, it will delete all your data. What is deleted here depends on
the system clock being set correctly. Having the user enter the desired
date might be better, but that too is subject to mis-typing. The best
thing I can think of at the moment is to rank your data by date and
choose a date from that which assures you the most recent 200, 500,
10000, or whatever number of records are most recent will not be deleted.
Test the date for your deletions against that. It must be a date prior
to your 500 most recent records.

A good question might be to consider for yourself why it is so necessary
to delete them at all. You could use a similar query mechanism to just
disregard them for reporting or forms purposes, and let them be. This
avoids the possible tragic loss of historic information, especially if
your backup scheme is not highly reliable. If at some future time you
want to change the number of days from 730 to what seems better at that
time, everything would adjust to the new limit immediately. If you set
it to 1000 days, you'd instantly have 270 days more information show up
(assuming there is some in your table). If table size (the 2 GB max for
Jet files, for example) or performance is your goal, then archiving them
first to a separate database would be an option. Ask yourself, if all
the data you have accumulated in this table were to disappear due to such
a malfunction, what amount of time and money would it cost? Now are you
motivated to protect it thoroughly?

Well, I've given you the rope to hang yourself. : ) What you do with
it is up to you!

Tom Ellison

Dear Tom,

First I thank you for all the help you have given me thus far. This
is a nasty problem!

The last SQL query you gave me (below) runs but gives the following
error message:

"Specify the table containing the records you want to delete"

If I make it a Select query it selects the correct clients!! But a
Delete query give the above error message.

One more tiny tweak maybe?

Thanks and take care...

Dear Ron:

Well, I used to be able to write queries. Actually, it's easy enough
when you have the database in front of you.

I think I had the order wrong, didn't I?

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730

I've been on my own problem almost straight for 48 hours. Please
forgive. I'm too tired, or old, or something.

Tom Ellison


Dear Tom,

Syntax error in HAVING clause

<Sigh>

Take care,

Dear Ron:

Maybe I should make excuses. Not.

OK, this may be what is required:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

Funny what you forget at times. I THINK this is what is needed.
Please get back, OK? This is sanity check day for me!

Tom Ellison


The second SQL statement creates an error that says you can't have
an aggregate function in a where clause as in
(MAX(meals.LastVisit)<Date()-730

The meals table contains the client master ID, a meals master ID,
(those two create the combined key for the meals table), a list of
date, one per row, of the dates the client has visited the food
bank. There
can be and often are multiple entries per client. The last row
contains the
last date a client came into the food bank. Soooo... if that date
is two or
more years old I want to delete the client and all records related
to this
client.




Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your
meaning of "LastVisit."

If you have several rows in [meals] each with a different
LastVisit, it is possible you want to delete the client and all
meals only when the most recent LastVisit is prior to 730 days ago.
Is that what you want?

The query as I read it would delete a client and related meals if
there is ANY visit more than 730 days ago. That would be a quite
different thing. It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows
what rows would be deleted if it were changed to be a DELETE query.
By not deleting anything, testing is easier, as you can change it
and run it again without having to restore the data every time it
deletes wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your
system date is off, you could be removing rows you do not intend.
That sounds like a bit of a risk to me.

Tom Ellison


This does not work. It selects many clients who have been in
within two years.
It does find a bunch of the "right" clients to delete but also
includes what seems be be random other clients not meeting the two
years since last time they came in.




message On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"

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));

The error message says: "Cannot delete from table".

You're trying too hard. Don't delete anything from the Meals
table -
cascading deletes will take care of that.

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


John W. Vinson[MVP]

 

[John Vinson]

Dear Ron:

Did you try the method of changing the query in the Design View, as I
suggested?

Or does this fix it:

DELETE client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730

Tom, I may be mistaken, but I think that any Totals operations such as
Group By or Max will prevent a query from being updateable - even if,
as in this case, it *should* be updateable.

It may be necessary to use a subquery such as:

DELETE * FROM Client
WHERE MasterID NOT IN
(SELECT DISTINCT MasterID FROM Meals
WHERE Meals.LastVisit > DateAdd("yyyy", -2, Date()));

to delete all clients except those who have an entry during the past
two years. That's a bit different from the criterion as posed though!
It may be safer to use TWO subqueries:

DELETE * FROM Client
WHERE MasterID NOT IN
(SELECT DISTINCT MasterID FROM Meals
WHERE Meals.LastVisit > DateAdd("yyyy", -2, Date()))
AND MasterID IN
(SELECT DISTINCT MasterID FROM Meals
WHERE Meals.LastVisit <= DateAdd("yyyy", -2, Date()));

to delete only those who *did* have a LastVisit over two years ago,
but not since.

John W. Vinson[MVP]


John W. Vinson[MVP]