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

[NeoPa]

missinglinq: I'm sure you are, Ade, but apparently some are not!

I'd hate to be confused with someone who thought that way ;)

[NeoPa]

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

I'm happy to explain :)

Generally, a thread is focused around the problem of the Original Poster (or OP). Answers of many varieties are encouraged. Further questions that are likely to take the focus away from the OP's requirements are considered unwelcome (they are against the rules in fact). In this case, your post was directing the thread away from the OP's question and onto your requirements. That is a hijack.

That said, we don't wish you to go unsupported, we simply want to keep the structure clean. You can post your question in it's own thread (as we now have) and all should be well.

I hope that makes it clearer for you.

[ahmedtharwat19]

thank you,

i`m sorry my english is not good. i think it very bad.

but i have a problem in this site because i add the thread

title:create a module via vba in current database or in another database

but kub365 was delete it , he think it`s a Homework/Coursework.

I`m an Accountant From 2001, and the vba(ms access) is just my hoppy.

please help me !!??..

(medo)