Check for duplicates

[vbvbvbvb]

Hello all,

I need your help please.

In table1 there are 2 fields: field1 & field2.
I am trying to check field1 for duplicate. If duplicates are found, I want to make sure field2 are the same. If field2 are different, generate a report.

How can I do this using SQL statement?

Returned Records should look like:
field1, field2
000001, ssssss
000001, sssss1
00dddd, 12kkkk
00dddd, 12hhhh

These 2 fields are string and no primary key are set.

Thank you so much.

[DerFarm]

You want to use the GroupBy option. If you are working in
Access, open a new select query with the table. Bring the
desired variables down. Click on the summation sign (looks like a
Greek S), and run the thing.

I think this will give you pretty much what you want.

[vbvbvbvb]

Thanks for tips but I want to use vb code & sql
strSQL = "Select....

and I don't think the Group By clause won't display duplicated field1. "ORDER BY" might work

[DerFarm]

write the thing in Access, cut and paste the sql code into a function, replace the variablenames that change with passed vars and return the results.

UPDATE AT2 INNER JOIN [tblMain Table(0)] ON AT2.QID = [tblMain 
Table(0)].QID SET [tblMain Table(0)].ATFPAGE_AGE = IIf([AT2].
[ATFPAGE]>"",IIf(IsNumeric([AT2].[ATFPAGE]),Val([AT2].
[ATFPAGE]),Null),Null);

was turned into the following three functions:

Function DoubleSQL(Table As TableStructure, _
                    Variable As VariableStructure) As String
'There are 2 kinds of double:  the Straight Numeric (regardless of integer or
'   decimal declaration in MedQuest, and NumericSets.  Numeric sets are rare, so will
'   ignored until the very last.
   Dim Ret_Val As String
   Dim UpdateSQL As String, OnSQL As String, SetSQL As String, DblSQL As String
   Dim str_Temp0 As String, str_Temp1 As String
   Dim DblRet As String, DblRetPost As String
   
   
     Ret_Val = ""
     UpdateSQL = BuildUpdateSQL(Table, Variable)
     OnSQL = BuildOnSQL(Table, Variable)
   
     DblRet = ""
     DblRetPost = ""
     If Variable.OriginalType = "31" Then
        DblRet = "Retrieve("
        DblRetPost = "," & Variable.AnalysisPosition & "," & Chr$(34) & ";" & Chr$(34) & ")"
     End If
     
     SetSQL = "SET [" & Variable.TableName & "].[" & Variable.AnalysisVarName & "]"
     SetSQL = SetSQL & "= IIf(" & DblRet & "[" & Table.OriginalTableName & "].[" & Variable.OriginalVarName & "]" & DblRetPost
     SetSQL = SetSQL & ">" & Chr$(34) & Chr$(34) & ","
     

     
     DblSQL = "IIf(isnumeric(" & DblRet & "[" & Table.OriginalTableName & "].[" & Variable.OriginalVarName & "]" & DblRetPost & ")"
     DblSQL = DblSQL & ",VAL(" & DblRet & "[" & Table.OriginalTableName & "].[" & Variable.OriginalVarName & "]" & DblRetPost & ")"
     DblSQL = DblSQL & ",Null),Null)"

     If Variable.AnalysisVarName > "" Then
        Ret_Val = UpdateSQL & " " & Chr$(10) & OnSQL & " " & Chr$(10) & SetSQL & " " & Chr$(10) & DblSQL & ";"
     End If
     
Xit_DoubleSQL:
    DoubleSQL = Ret_Val
End Function
Function BuildOnSQL(Table As TableStructure, _
                    Variable As VariableStructure) As String

                  
'OnSQL is the portion of the Update Query that deals with linking the New Table
'   to the values in the old table.

   Dim Ret_Val As String
   Dim OnSQL As String
   Dim str_Temp0 As String, str_Temp1 As String

     OnSQL = " ON "
       
     str_Temp0 = Table.LinkingIDList
     While str_Temp0 > ""
        str_Temp1 = Retrieve(str_Temp0, 1, TertyDelimiter)
        OnSQL = OnSQL & "[" & Table.OriginalTableName & "].[" & str_Temp1 & "]"
        OnSQL = OnSQL & "="
        OnSQL = OnSQL & "[" & Variable.TableName & "].[" & str_Temp1 & "]"
        OnSQL = OnSQL & " AND "
        str_Temp0 = Delete(str_Temp0, 1, SecndDelimiter)
     Wend
     Ret_Val = NoNulls(OnSQL, " AND ")

Xit_BuildOnSQL:
    BuildOnSQL = Ret_Val
End Function
Function BuildUpdateSQL(Table As TableStructure, _
                        Variable As VariableStructure) As String
'The Update portion of the Update Query is a constant
   Dim Ret_Val As String
   Dim UpdateSQL As String
     
     Ret_Val = ""
     UpdateSQL = "UPDATE [" & Table.OriginalTableName & "] INNER JOIN ["
     UpdateSQL = UpdateSQL & Variable.TableName & "]"
     
     If Variable.TableName > "" Then
        Ret_Val = UpdateSQL
     End If

Xit_BuildUpdateSQL:
    BuildUpdateSQL = Ret_Val
End Function

oh, and some imagination is required.

[vbvbvbvb]

Thanks for your time. I already got the answer but I will give your code a try later...

thanks a bunch