Trying to change Select into Delete Query

[shaggy]

I'm trying to change a select query to a delete query and can't seem to get it to work.

I append records to my table [Keys] daily and one of the fields is the date the record was appended [Updated].

If there are records of a given [Cycle] with different [Updated] dates I want to delete all records of that [Cycle] that don't have the most recent [Updated] date.

My select query selects the records I want to delete, but when I change it to a delete query I get the error "Specify the table containing the records you want to delete".

My first query gives me the Max [Updated] for a given [Cycle].

Delete Old Key Info 1
SELECT DISTINCT Keys.Cycle, Max(Keys.Updated) AS MaxOfUpdated
FROM Keys
GROUP BY Keys.Cycle;

My second query combined with the first gives me the records I want to delete.

Delete Old Key Info
SELECT Keys.Cycle, Keys.Route, Keys.[A/S], Keys.Address, Keys.Prefix, Keys.[Meter #], Keys.Key, Keys.Updated
FROM [Delete Old Key Info 1] INNER JOIN Keys ON [Delete Old Key Info 1].Cycle = Keys.Cycle
WHERE (((Keys.Updated)<>[MaxOfUpdated]));

I think the problem is that the query is trying to delete from the first query instead of the table.

Any help would be appreciated.

 

[Jon K]

Use a subquery:-

DELETE Keys.*
FROM Keys
WHERE Cycle & Updated not in (Select Cycle & MaxOfUpdated from [Delete Old Key Info 1]);