Find Duplicate Column Values for a single row in Table

0
votes

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,

2
sqlms-access
Your query seems simple enough. What is the problem with it? – Gordon Linoff
My Issue is I have taken a simple example with 4 Fields But in real time I have to handle more than 20 Similar Columns – Somu

2 Answers

1
votes

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.

0
votes

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.