Deleting earlier instances of a record

[JezzaHyde]

Hey folks,

Don't know if what i'm trying to do here is possible...but i'll ask it anyway.

I'm looking for a way to delete records in a table that have the same ID# but have different dates associated.
Eg;

ID Date Value
1 28/08/02 ABC
2 28/08/02 XYZ
1 29/08/02 JKL

In this instance...the first record would be deleted, as it is the oldest instance of the record with ID# 1

As usual, i'm sure there are many differents ways to achieve this, and any help is welcomed

Cheers

-Jezza

 

[FoxProProgrammer]

Hi Jezza,

Give this a try:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MaxDate As Date

Set db = CurrentDb
Set rs = db.OpenRecordset("yourtablename", dbOpenDynaset)

MaxDate = DMax("Date", "yourtablename", "ID= 1&quot

rs.FindFirst "ID = 1 And Date = #" & MaxDate & "#"

If rs.NoMatch = False Then
rs.Delete
End If

End Sub

Of course you could substitute the value of a control for ID = 1 instead of hard coding it.

Regards,
dz
dzaccess@yahoo.com

 

[danvlas]

The query:

DELETE YourTable.*
FROM Attendance
WHERE (((YourTable.ID) In (SELECT YourTable.ID
FROM YourTable
GROUP BY YourTable.ID
HAVING (((Count(YourTable.ID))>1))) AND ((YourTable.DateField) In (SELECT Min(YourTable.DateField) AS OldDate
FROM YourTable
GROUP BY YourTable.ID
HAVING (((Count(YourTable.ID))>1))));

will delete the oldest record from duplicates found on ID.

Regards,

Dan

 

[cmmrfrds]

delete from yourtable A
where A.Date <
(select max(Date) from yourtable B
where A.ID = B.ID)

 

[danvlas]

Right, cool and simple...
But I have one note: if there are more than 2 dups, the SQL will leave only one record. If you want to delete only one record, you need more parameters to identify that particular record.

Regards,

Dan

 

[danvlas]

Typing error in my post:
&quot;Attendance&quot; should be replaced with &quot;YourTable&quot;

Regards,

Dan

 

[JezzaHyde]

Thanks for the help folks.

As it turns out, i managed to fix the problem earlier on in the piece (by forcing a new record to overwrite the existing record if the id already exists in the table). But thanks for the help anyway

Cheers

-Jezza