Check for duplicates

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.

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.

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

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.

Code:

---

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:

Code:

---

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.

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

thanks a bunch