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


"Ron Le Blanc" wrote in message
...
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.




"John Vinson" wrote in message
...
On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"
wrote:

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]