CODEWITHSUNDEEP
sqlms-access
Somu
Somu

Reputation: 3

Find Duplicate Column Values for a single row in Table

Hi I have a Access DB Table With Multiple Columns (Say Col_1, Col_2,Col_3,......,Col_n )all Integer

I need a query to find for any Specific Row The Values of the Multiple Column is Duplicated,

Take for Example Below is the Table

Field2  Field3  Field4  Field1
 1        3       2       3
 4        3       1       2
 5        6       7       8
 2        2       3       1 
 3        6       5       4
 3        3       3       2
                  2       1
                  2       2

Now I need a query To Display the Below result

 Field2 Field3  Field4  Field1
  1       3        2      3
  2       2        3      1
  3       3        3      2
                   2      2

Currently I am using the Query ("Select * from Table1 where Field1 in (Field2,Field3,Field4) or Field2 in (Field1,Field3,Field4) or Field3 in (Field2,Field1,Field4) or Field4 in (Field2,Field3,Field1)") to fetch the result But I need a simple Query to resolve it,

Upvotes: 0

Views: 616

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

As a note, you can simplify your query a little bit:

Select *
from Table1
where Field1 in (Field2, Field3, Field4) or
      Field2 in (Field3, Field4) or
      Field3 in (Field4);

You only need to compare the fields to the rest in the row. In other words, the expression Field4 in (Field1, Field2, Field3) is redundant, because you have already compared each of those fields to Field4.

Upvotes: 1

VBlades
VBlades

Reputation: 2251

Wrote this quick function which you save in a Module:

Public Function HasDupes(ParamArray vals()) As Boolean

Dim dic As Object
Dim v As Variant

Set dic = CreateObject("Scripting.Dictionary")

HasDupes = False

For Each v In vals
    If IsNull(v, "") = False Then
        If dic.Exists(v) = True Then
            HasDupes = True
            Exit For
        Else
            dic.Add v, ""
        End If
    End If
Next

Set dic = Nothing

End Function

And here's the query:

SELECT Field2, Field3, Field4, Field1
FROM Table1
WHERE (((HasDupes([Field2],[Field3],[Field4],[Field1]))=True));

The function takes as many fields as you want to feed it. It then leverages a Dictionary object to find unique values, as Dictionary keys must be unique. If there are dupe values, the function returns True, and we know that row should be presented in the query.

EDIT: Changed the function to deal with nulls.

Upvotes: 0

Related Questions