Query to find duplicate item in 2 table

I have this table

Antecedent           Consequent
I1                   I2                   
I1                   I1,I2,I3
I1                   I4,I1,I3,I4
I1,I2                I1
I1,I2                I1,I4
I1,I2                I1,I3
I1,I4                I3,I2
I1,I2,I3             I1,I4
I1,I3,I4             I4

AS you can see it's pretty messed up. is there anyway i can remove rows if item in consequent exist in antecedent (in 1 row)

for example: INPUT:

Antecedent           Consequent
I1                   I2                   
I1                   I1,I2,I3    <---- DELETE since I1 exist in antecedent
I1                   I4,I1,I3,I4 <---- DELETE since I1 exist in antecedent
I1,I2                I1          <---- DELETE since I1 exist in antecedent
I1,I2                I1,I4       <---- DELETE since I1 exist in antecedent
I1,I2                I1,I3       <---- DELETE since I1 exist in antecedent
I1,I4                I3,I2       
I1,I2,I3             I1,I4       <---- DELE开发者_运维百科TE since I1 exist in antecedent
I1,I3,I4             I4          <---- DELETE since I4 exist in antecedent

OUTPUT:

Antecedent           Consequent
I1                   I2  
I1,I4                I3,I2            

is there anyway i can do that by query?

I cannot see how this can be done by an Access query without a user defined function, which suggests that it may be easier to simply use VBA to delete the problem rows.

Rico, everyone is right about the UDF, to do it with queries would probably be slower. Add this function to a module and see how fast it is. I ran it on 500K records in a few seconds.

Public Function IsWithin(ByVal vAntecedent As Variant, ByVal vConsequent As Variant) As Boolean
    On Error GoTo ErrQuit
    IsWithin = False
    If Not IsNull(vAntecedent) Then
        If Not IsNull(vConsequent) Then
            Dim aryAnt As Variant, aryCons As Variant
            Dim vAnt  As Variant, vCons  As Variant
            aryAnt = Split(vAntecedent, ",")
            aryCons = Split(vConsequent, ",")
            For Each vAnt In aryAnt
                For Each vCons In aryCons
                    If vAnt = vCons Then
                        IsWithin = True
                        Exit For
                    End If
                Next
                If IsWithin Then Exit For
            Next

        End If
    End If
    Exit Function
ErrQuit:
    IsWithin = False
End Function

Your query might look something like this:

SELECT Antecedent, Consequent
FROM Table1
WHERE IsWithin([Antecedent],[Consequent])=False;