Delete Query Problem

[Ron Le Blanc]

I have a database with multiple tables. The main table contains client data.
A "meals" table lists the meals that a client has received and when the
last time the client was in. I want to delete any and all records where the
"Lastvisit" in the meals table is <Date()-730, two years old.

The following is the SQL that the Delete Query generates. When viewed in the
table view the correct records are shown. When I try to run the query it
says I must specify the table to delete from.

Here is the SQL:

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

What am I doing wrong?

Thanks for any help!

 

[John Spencer]

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)