comparing records

[realone]

hi guys,
pls can u help me with an algorithm that we compare record in a database one after the other.As in the first record compare to other records in the database,second record also compare compare with other records in the db,.....................untill all the records are compare.

i want to generate report on record that appear more than once

[dadelsen]

You could use a SQL statement with a GROUP BY ... HAVING clause.
Group the records by the columns that are relevant for "equality". Use HAVING COUNT(*) > 1 to return only those groups that have more than one member = more than one row with the same values in the relevant columns.

Private Sub Command1_Click()
Dim aConn As ADODB.Connection
Set aConn = New ADODB.Connection
aConn.ConnectionString = "DSN=XYZ;"
aConn.Open

Dim rs As ADODB.Recordset
Dim sSQL As String
sSQL = "SELECT col1, col2 FROM t1 " & _
    "GROUP BY col1, col2 " & _
    "HAVING COUNT(*) > 1"

Set rs = New ADODB.Recordset
Call rs.Open(sSQL, aConn)
Do While Not rs.EOF
   MsgBox "More than one record with columnvalues  Col1:" & rs(0) & " Col2:" & rs(1)
   rs.MoveNext
Loop
rs.Close

End Sub

[debasisdas]

As suggested ,that can be done at database level. No need to any special aglorithm for that.

[realone]

Thanks very much for the code it will go along way to help.But if i have to be honest with u.i actually need thesame code that will does for image record(Fingerprint). i want to compare one fingerprint to another just like the one u sent for the text field records.

[dadelsen]

Please check if the dbms supports grouping for the datatype that is used for the fingerprint column.

If it does not and there is a key in the table to identify each record, ýou may use two loops, as shown below. This solution has a very long runtime if there are many records in the table.

Private Sub Command2_Click()
Dim aConn As ADODB.Connection
Set aConn = New ADODB.Connection
aConn.ConnectionString = "DSN=VPE;"
aConn.Open
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim sDups As String
Dim sSQL1 As String
Dim sSQL2 As String
Dim iCnt As Integer

sDups = ""
iCnt = 0
sSQL1 = "SELECT keycol, fprintcol FROM table1 ORDER BY keycol"
Set rs1 = New ADODB.Recordset
Call rs1.Open(sSQL1, aConn)
Do While Not rs1.EOF
   sSQL2 = "SELECT keycol, fprintcol FROM table1 WHERE keycol <> '" & rs1!keycol & "'"
   Set rs2 = New ADODB.Recordset
   Call rs2.Open(sSQL2, aConn)
   Do While Not rs2.EOF
      'comparing, for example:
      If rs2!fprintcol = rs1!fprintcol Then 
          'do not remember the same key multiple times
          If InStr(sDups, "|" & Trim(rs1!keycol)) = 0 Then
              sDups = sDups & "|" & Trim(rs1!keycol)
              iCnt = iCnt + 1
          End If
      End If
      rs2.MoveNext
   Loop
   rs1.MoveNext
Loop
MsgBox Replace(sDups, "|", vbLf)
End Sub