Need help to remove "duplicates"

[Sunshack]

Hi!



I have a table that contains various item-combinations, but they are always duplicated as shown in the table below. The duplicates are paired with different colors. (technically they are not duplicates as they are in different columns, but the same items appear twice as you can see)

Item1	Item2
A	B
B	A
A	C
C	A
B	C
C	B
F	B
B	F

My table has too many rows to clean these out manually.
I need a clever way to get rid of these duplicates, so I am only left with a table with one of each combinations.
It does not matter what version of the combination I am left with, as long as its one of them.

Can I do this with Access?

All help is highly appreciated!

[ranman256]

make a col in the table called: MARK
In a query, qsDupeList, add this column:
DupeFld: Iif(item2<item1,item2 & item1,item1 & item2)
uses the code below to mark the duplicates.
sort the query:

DupeFld, item1

delete the marked ones if needed.

usage:
RemoveDuplicates "qsDupeList", "DupeFld","Mark"

put this code into a module

Public Sub RemoveDuplicates(ByVal pvQry, ByVal pvDupeFld, ByVal pvChgFld)    
'pvQry = query name
'pvDupeFld   = field with duplicate values
'pvChgFld    = field to change when duplicate is found
Dim vMsg
Dim db As Database
Dim rst   'As Recordset
Dim qdf As QueryDef
Dim vCurrDup, vPrevDup, vCurrFld, vAddr
'DoCmd.Hourglass True
       
Set db = CurrentDb
Set qdf = db.QueryDefs(pvQry)
Set rst = qdf.OpenRecordset(dbOpenDynaset)
vPrevDup = "*&%"
With rst
    While Not .EOF
        vCurrDup = .Fields(pvDupeFld) & ""
        vCurrFld = UCase(.Fields(pvFld2Check)) & ""
        
        If vCurrDup <> "" Then
                '-----------------------
                'MARK THE DUPES...
                '-----------------------
              If vPrevDup = vCurrDup And vPrevFld = vCurrFld Then         'mark this
                    .Edit
                    .Fields(pvChgFld) = "D"
                    .Update
              End If
        End If
        vPrevDup = vCurrDup
        vPrevFld = vCurrFld
        
       .MoveNext
    Wend
End With
Set qdf = Nothing
Set rst = Nothing
Set db = Nothing
Exit Sub
ErrRemove:
MsgBox Err.Description, , mkCLASSNAME & "::RemoveDuplicates():" & Err
End Sub

[Sunshack]

Hi!

Thank you so much for your reply! I have to admit, your solution this is WAY above my Access-knowledge I made the module with the code, but honestly i have no idea how to apply it :P


Your idea of chaining them together with that formula did however give me something i could paste into excel, divide the column (text to columns), then remove the duplicates.
Seems like that left me with the result i was looking for!

If you have any tips as to how i can make the module work, or if you know a useful guide for modules, that would be much appreciated! Im always looking to expand my access-horizon