Delete Query

[Matt Dawson]

I currently have a list of data and have found Serial ID's with related
problems on different dates. What I need to do is list all the Serial ID's
and delete all the old records and keep only the most recent one. I have run
a query to select the duplicate Serial IDs and have found the related dates.
How do i run a delete query to erase all records per Serial ID bar the most
recent one
Any help would be much appreciated
Matt

 

[Alasdair]

You only want to keep the most recent record? Are you sure you want to delete
the old records instead of archiving? if so.... if you know your serial ID
you wish to keep in the criteria type <> "Serial ID you wish to keep"

Then change the query type to a delete query.

regards

al

 

[Matt Dawson]

Al,
Sorry i think we got our wires crossed. Each Serial ID has been repaired on
and therefore has many old problems. I only want to keep the most recent
problem and delete the rest of them from my records. However there are
multiple Serial IDs with multiple problems if you understand

Matt

 

[John Spencer]

So you want to keep those with the most recent date - Max(DateField).

DELETE DISTINCT ROW YourTable.X
FROM YourTable.X
WHERE Cstr(SerialID) & CStr(RepairDate) NOT IN (
SELECT Cstr(SerialID) & CStr(RepairDate)
FROM YourTable as X
WHERE X.RepairDate =
(SELECT MAX(RepairDate)
FROM YourTable as Y
WHERE Y.SerialID = X.SerialID))


That will be SLOW on large recordsets.

Do you have a single field primary key on the records in the table? If so,
you could rewrite the above.

The following MAY work and if it does it will be faster.
DELETE DISTINCT ROW YourTable.SerialID
FROM YourTable LEFT JOIN (
SELECT SerialID, RepairDate
FROM YourTable as X
WHERE X.RepairDate =
(SELECT MAX(RepairDate)
FROM YourTable as Y
WHERE Y.SerialID = X.SerialID)) as Keep
ON YourTable.SerialID = Keep.SerialID
AND YourTable.RepairDate = Keep.RepairDate
WHERE Keep.SerialID is Null and Keep.RepairDate is Null

TRY this on a COPY of your database. This cannot be undone.