如何在MS Access中根据日期删除多个重复项

SELECT DateField, COUNT(DateField) AS NumOccurrences
FROM YourTable
GROUP BY DateField
HAVING COUNT(DateField) > 1;

DELETE FROM YourTable
WHERE ID NOT IN (
    SELECT MIN(ID)
    FROM YourTable
    GROUP BY DateField
);

CREATE TABLE YourTable (
    ID AUTOINCREMENT PRIMARY KEY,
    DateField DATETIME,
    OtherField TEXT
);

Sub DeleteDuplicateDates()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    
    Set db = CurrentDb
    
    ' 创建一个查询来找出重复的日期
    strSQL = "SELECT DateField, COUNT(DateField) AS NumOccurrences " & _
             "FROM YourTable " & _
             "GROUP BY DateField " & _
             "HAVING COUNT(DateField) > 1;"
    
    ' 创建一个临时查询来存储最小的ID
    Dim tempQueryName As String
    tempQueryName = "TempMinIDs"
    Set qdf = db.CreateQueryDef(tempQueryName, _
                                "SELECT DateField, MIN(ID) AS MinID " & _
                                "FROM YourTable " & _
                                "GROUP BY DateField;")
    
    ' 执行删除操作
    strSQL = "DELETE FROM YourTable " & _
             "WHERE ID NOT IN (" & tempQueryName & ");"
    
    db.Execute strSQL
    
    ' 删除临时查询
    db.QueryDefs.Delete tempQueryName
    
    MsgBox "重复的日期已被删除。"
End Sub