Deleting old entries using date funtion

[sudhirhinduja]

Hello,

I have generated the duplicate Executives from a query using the following code:

SELECT [Companies and Executives with Proxy Date Query].[Name], [Companies and Executives with Proxy Date Query].[ExecID], [Companies and Executives with Proxy Date Query].[Title], [Companies and Executives with Proxy Date Query].[Company], [Companies and Executives with Proxy Date Query].[ProxDate]
FROM [Companies and Executives with Proxy Date Query]
WHERE ((([Companies and Executives with Proxy Date Query].Name) In (SELECT [Name] FROM [Companies and Executives with Proxy Date Query] As Tmp GROUP BY [Name] HAVING Count(*)>1 )))
ORDER BY [Companies and Executives with Proxy Date Query].[Name];

I want to delete the duplicate entries, but the older ones while retaining the latest ones based on ProxDate.

Please advise.

Thanks,
Sudhir.

 

[gblack]

This might be one way

I don’t claim to be an VBA guru, but this worked for me. What I did was table my find duplicate query and change it into a Make table query to get the duplicate values into the table. I had the query sort by the value I wanted (e.g. you would want the query to sort the values by proxy ExecID.

Then I made a module and pulled up that table in a record set. Referencing each record against the next. And deleting the first one if the values were equal. So I sorted in descending order so that the duplicate record that I wanted to keep was always the last in the set of dupes for said record.

Dim db As DAO.Database
Dim tbl, dName, dExecID, dTitle As String
Dim rst, rs As DAO.Recordset
Set db = CurrentDb

'Get table Name
tbl = "Name of the table you make"

Set rst = db.OpenRecordset(tbl)
rst.MoveFirst
dName = rst!NAME
dExecID = rst!ExecID
dTITLE = rst!TITLE
rst.MoveNext

Do Until rst.EOF
If dName = rst!NAME And dExecID = rst!ExecID _
And dTITLE = rst!TITLE Then
rst.Delete
Else
dName = rst!NAME
dExecID = rst!ExecID
dTITLE = rst!TITLE
End If
If Not rst.EOF Then
rst.MoveNext
End If
Loop
…
So this should loop through your table and get rid of the first duplicate record, so make sure you set the sort in your make table query appropriately.
Anyway, like I said, I’m not a guru, but this worked for me. Maybe some of those wizards out there can help you better…or maybe this will give you another angle…

GL,
Gary