Delete Other Records

[Guest]

I have a query that selects the top value from a table (See below). I want
to create a query deletes all the record that less than (<) the top value.

SELECT TOP 1 [Request ID Number].ID, [YearID] & "-" & [ID] & "-" & [RDU] AS
[Request ID]
FROM [Request ID Number]
ORDER BY [Request ID Number].ID DESC;

Thanks for any help.

 

[John Spencer]

Use that qquery as a subquery

DELETE DistinctRow [Request Id Number].ID
FROM [Request ID Number]
WHERE [Request ID Number].ID Not IN (
SELECT TOP 1 [Request ID Number].ID
FROM [Request ID Number]
ORDER BY [Request ID Number].ID DESC)

Although I would think using DMAX would be easier
DELETE DistinctRow [Request Id Number].ID
FROM [Request ID Number]
WHERE [Request ID Number].ID < DMAX("ID","[Request ID Number]")

The square brackets may not be needed in the DMAX statement and may cause an
error.