Delete Other Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
Back
Top