如何在MS Access中基于日期去重并保留最新记录及优化方案

Hey there! Your current approach works, but manually looping through those three queries is a total hassle—and not the most efficient way to handle this. Let's fix that with a one-step solution, plus cover how to automate your existing workflow if you really need it.

The Problem Recap #

You have Table1 with duplicate names (Field1 + Field2) and want to keep only the record with the latest date (Field3), deleting all older duplicates. Your current method requires repeating three queries until no more duplicates are left, which is tedious.

Better Approach: One Query to Get Clean Data #

Instead of looping, we can use a subquery to directly identify the latest record for each name, then either create a clean table or delete the duplicates in place.

Option 1: Create a Clean Table (Safe & Easy) #

This creates a new table with only the latest records, leaving your original table intact until you're ready to replace it:

-- Create a clean table with latest records per name
SELECT t1.ID, t1.Field1, t1.Field2, t1.Field3 INTO Table1_Clean
FROM Table1 AS t1
INNER JOIN (
    -- Get the latest date for each unique name
    SELECT Field1, Field2, Max(Field3) AS LatestDate
    FROM Table1
    GROUP BY Field1, Field2
) AS t2 
ON t1.Field1 = t2.Field1 
AND t1.Field2 = t2.Field2 
AND t1.Field3 = t2.LatestDate;

-- Optional: Replace original table with clean version
DROP TABLE Table1;
RENAME TABLE Table1_Clean TO Table1;

Option 2: Delete Duplicates Directly in Table1 #

If you prefer to modify the original table without creating a new one, use this delete query:

DELETE DISTINCTROW Table1.*
FROM Table1
WHERE NOT EXISTS (
    SELECT 1
    FROM (
        SELECT Field1, Field2, Max(Field3) AS LatestDate
        FROM Table1
        GROUP BY Field1, Field2
    ) AS t2
    WHERE Table1.Field1 = t2.Field1 
      AND Table1.Field2 = t2.Field2 
      AND Table1.Field3 = t2.LatestDate
);

Handling Edge Cases #

If multiple records for the same name have the exact same latest date and you want to keep only one (e.g., the one with the highest ID), adjust the subquery to include Max(ID):

SELECT t1.ID, t1.Field1, t1.Field2, t1.Field3 INTO Table1_Clean
FROM Table1 AS t1
INNER JOIN (
    SELECT Field1, Field2, Max(Field3) AS LatestDate, Max(ID) AS LatestID
    FROM Table1
    GROUP BY Field1, Field2
) AS t2 
ON t1.Field1 = t2.Field1 
AND t1.Field2 = t2.Field2 
AND t1.Field3 = t2.LatestDate
AND t1.ID = t2.LatestID;

Automating Your Original Workflow (If You Must) #

If you need to stick with your original three-query approach but want it to run automatically, use this VBA macro in Access:

Sub AutoDeleteDuplicates()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim recordsToDelete As Integer
    
    Set db = CurrentDb()
    
    Do
        -- Run Query 1: Create sorted Table2
        db.Execute "SELECT Table1.Field1, Table1.Field2, Table1.Field3, Table1.ID INTO Table2 FROM Table1 GROUP BY Table1.Field1, Table1.Field2, Table1.Field3, Table1.ID ORDER BY Table1.Field1 DESC , Table1.Field2 DESC , Table1.Field3 DESC;"
        
        -- Run Query 2: Identify duplicates to delete
        db.Execute "SELECT Table2.Field1, Table2.Field2, Count(Table2.ID) AS CountOfID, Min(Table2.ID) AS MinOfID INTO Temp_DeleteThese FROM Table2 GROUP BY Table2.Field1, Table2.Field2 HAVING (((Count(Table2.ID))>1));"
        
        -- Check if there are records to delete
        Set rs = db.OpenRecordset("SELECT Count(*) AS Total FROM Temp_DeleteThese;")
        recordsToDelete = rs!Total
        rs.Close
        
        If recordsToDelete > 0 Then
            -- Run Query 3: Delete duplicates from Table1
            db.Execute "DELETE DISTINCTROW Table1.* FROM Temp_DeleteThese INNER JOIN Table1 ON Temp_DeleteThese.MinofID = Table1.ID;"
        End If
        
        -- Clean up temporary tables
        On Error Resume Next
        db.Execute "DROP TABLE Table2;"
        db.Execute "DROP TABLE Temp_DeleteThese;"
        On Error GoTo 0
        
    Loop While recordsToDelete > 0
    
    Set rs = Nothing
    Set db = Nothing
    
    MsgBox "Duplicate cleanup finished!", vbInformation
End Sub

To use this:

• Open your Access database
• Press Alt + F11 to open the VBA editor
• Insert a new module
• Paste this code
• Run the AutoDeleteDuplicates sub

Why the One-Step Method Is Better #

• Faster: No repeated table creation/deletion or loop iterations
• Less error-prone: Avoids manual intervention or missed duplicates
• Cleaner: No temporary tables cluttering up your database

内容的提问来源于stack exchange，提问作者kdudeIA