Solved How to delete only the oldest (duplicated) records? (1 Viewer)

[XelaIrodavlas]

I have a table (tblSchedules) which contains any number of duplicate values, and I'm trying to write some SQL to delete all but the newest record (the one with the largest auto-ID). I thought I had it cracked, but I'm getting the error "Could not delete from specified tables". Can anyone see what I'm doing wrong? Here's what I did:

first, I identified the oldest records:
Query "KeepDuplicatesMaxID"

SELECT DISTINCT tblSchedules.EmployeeName, tblSchedules.SchedDate, Max(tblSchedules.SchedID) AS MaxOfSchedID
FROM tblSchedules
GROUP BY tblSchedules.EmployeeName, tblSchedules.SchedDate
HAVING (((Count(tblSchedules.SchedID))>1));

second, using this as a subquery I compare it to the original table and identify the remaining older duplicates (which I want to delete). See below, unfortunately while I can preview the records, once I click run it doesn't work and returns the rather unhelpful "Could not delete from specified tables" error.

Query "dltOldestDuplicates"

DELETE DISTINCTROW tblSchedules.*, KeepDuplicatesMaxID.MaxOfSchedID
FROM tblSchedules LEFT JOIN KeepDuplicatesMaxID ON tblSchedules.SchedID = KeepDuplicatesMaxID.MaxOfSchedID
WHERE (((KeepDuplicatesMaxID.MaxOfSchedID) Is Null));

Any ideas?

I have set Unique records on both queries to yes and there are no enforced relationships anywhere (it's a new environment I made just to test this).

TIA

 

[arnelgp]

you can try this:

DELETE Schedules.SchedID, *
FROM Schedules
WHERE
(((Schedules.SchedID)<>DMax("SchedID","Schedules","EmployeeName='" & [EmployeeName] & "' And SchedDate=#" & Format([SchedDate],"m/d/yyyy") & "#")));

 

[ebs17]

DELETE
FROM
   tblSchedules AS T
WHERE
   EXISTS
      (
         SELECT
            NULL
         FROM
            tblSchedules AS X
         WHERE
            X.SchedID > T.SchedID
              AND
            X.EmployeeName = T.EmployeeName
               AND
            X.SchedDate = T.SchedDate
      )

DELETE DISTINCTROW
   T.*
FROM
   tblSchedules AS T
      INNER JOIN tblSchedules AS X
      ON T.EmployeeName = X.EmployeeName
         AND
      T.SchedDate = X.SchedDate
WHERE
   T.SchedID < X.SchedID

 

[XelaIrodavlas]

Thanks both,

Arnel I couldn't get your solution to work though I can see what it's trying to do.

ebs your first solution worked well.

Many thanks