Delete Duplicated Rows

[ahmedtharwat19]

hi, every one

for delete duplicated rows can any one up to us an example to see that

because i`m beginning to ms access

and i have a problem about that

thank you for all
abo mroan

[NeoPa]

When posting your question it is necessary to put it in its own thread. Hijacking somebody else's (Delete Duplicate records) is not acceptable. It is acceptable to post a link if you think it's relevant though. I have split it away for you (and included a link across), but please remember in future.

[NeoPa]

If you had read the previous thread you would have seen that posts #9 & #10 have examples of code for this job. Unfortunately, as you haven't explained what you need other than very generally, I cannot say how well suited either is for your needs. In case it helps though (and for anyone finding this in a search) I will include my code from Post #10 of Delete Duplicate records.

This code assumes a table in the local db but it can be tweaked or enhanced.
For instance, if the one to be kept must have the lowest Primary Key value then it could be changed to do that.

'DelDups Removes duplicate records in strTable matched on strField
Public Sub DelDups(strTable As String, strField As String)
    Dim strSQL As String, varLastVal As Variant
 
    'Recordset must be full table but sorted by the field we're checking
    strSQL = "SELECT * FROM [" & strTable & "] ORDER BY [" & strField & "]"
    With CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        varLastVal = Null
        'For each record, check against previous value in strField
        'If same then this is a duplicate so can be removed
        Do Until .EOF
            If .Fields(strField) = varLastVal Then
                Call .Delete
            Else
                varLastVal = .Fields(strField)
            End If
            Call .MoveNext
        Loop
    'Ending the 'With' releases the Recordset
    End With
End Sub

Hope it helps.

[ahmedtharwat19]

Mr NeoPa

it`s a perfect code thank you very much

but what the condition if there 2 fields

i try it and it work as well

'DelDups Removes duplicate records in strTable matched on strField
Public Function DelDups(strTable As String, strField As String, strField1 As String)
    Dim strSQL As String, varLastVal As Variant, varLastVal1 As Variant
 
    'Recordset must be full table but sorted by the field we're checking
    strSQL = "SELECT * FROM [" & strTable & "] ORDER BY [" & strField & "] , [" & strField1 & "]"
    With CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        varLastVal = Null
        varLastVal1 = Null
 
        'For each record, check against previous value in strField
        'If same then this is a duplicate so can be removed
        Do Until .EOF
            If .Fields(strField) = varLastVal And .Fields(strField1) = varLastVal1 Then
                Call .Delete
            Else
                varLastVal = .Fields(strField)
                varLastVal1 = .Fields(strField1)
 
            End If
            Call .MoveNext
        Loop
    'Ending the 'With' releases the Recordset
    End With
End Function

thank you

[missinglinq]

When posting your question it is necessary to put it in its own thread. Hijacking somebody else's (Delete Duplicate records) is not acceptable.

Careful, NeoPa! You appear to be discipling someone in public!

Linq ;0)>

[NeoPa]

Ah well Linq my friend. I'm still sane aren't I.

[NeoPa]

ahmedtharwat19: but what the condition if there 2 fields

i try it and it work as well

Indeed. It seems you have understood the concept well enough to modify it for slightly changed circumstances.

Well done :)

[ahmedtharwat19]

@NeoPa
yes, thank you for all.

[missinglinq]

@NeoPa
I'm sure you are, Ade, but apparently some are not!

Linq ;0)>

[ahmedtharwat19]

@NeoPa
i`m sorry idont understand what do you mean , i just add this for that thread to complete the thread in same problem.

Generally, thank you for an advice.

Best Regards ,
Medo