title: SQL: How to query the distinct values for all columns
body: <p>For example,
  The table has 3 rows and 3 columns:</p>
  
  <p><b>Name Age Gender</b>
  <br/>
  Peter 25 M 
  <br/>
  John 29 M 
  <br/>
  Alex 25 M</p>
  
  <p>And I want to query the table and get</p>
  
  <p><b>Name  Age Gender</b>
  <br/>
  Peter 25 M
  <br/>
  John  29
  <br/>
  Alex    </p>
  
  <p>The method I have tried:</p>
  
  <pre><code>SELECT DISTINCT Name,Age,Gender FROM table
  </code></pre>
  
  <p>The output is still</p>
  
  <p><b>Name Age Gender</b>
  <br/>
  Peter 25 M 
  <br/>
  John 29 M 
  <br/>
  Alex 25 M</p>
  
  <p>How to achieve the table that there is no redundant entries for every field? Thanks.</p>
  
  <p>Thanks for the help from all of you, especially the help from donPablo.
  Here's my VBA code to achieve that. Since I am totally new to VBA, the code might not be very clean and efficient. But at least it works.</p>
  
  <pre><code>Option Compare Database
  
  Sub ReadDistinctValue()
  
  Dim d As Database
  Dim rs As Recordset
  Dim FN As Field, Age As Field, Sex As Field
  
  Set d = CurrentDb()
  Set rs = d.OpenRecordset("Table1")
  Set FN = rs.Fields("FN")
  Set Age = rs.Fields("Age")
  Set Sex = rs.Fields("Sex")
  
  d.Execute "CREATE TABLE Table4 (FN Text,Age Text,Sex Text)"
  
  While Not rs.EOF
      If CheckFN(FN) = False Then
          Call WriteFN(FN)
      End If
      If CheckAge(Age) = False Then
          Call WriteAge(Age)
      End If
      If CheckSex(Sex) = False Then
          Call WriteSex(Sex)
      End If
      rs.MoveNext
  Wend
  
  rs.Close
  
  End Sub
  
  Function CheckFN(FN As Field) As Boolean
  
  Dim d As Database
  Dim rs_new As Recordset
  Dim FN_new As Field
  
  Set d = CurrentDb()
  Set rs_new = d.OpenRecordset("Table4")
  Set FN_new = rs_new.Fields("FN")
  
  CheckFN = False
  
  Do While Not rs_new.EOF
      If FN_new = FN Then
          CheckFN = True
          Exit Do
      End If
      rs_new.MoveNext
  Loop
  
  rs_new.Close
  
  End Function
  
  Function WriteFN(FN As Field)
  Dim d As Database
  Dim rs_new As Recordset
  Dim FN_new As Field
  
  Set d = CurrentDb()
  Set rs_new = d.OpenRecordset("Table4")
  Set FN_new = rs_new.Fields("FN")
  
  If Not rs_new.EOF Then
      rs_new.MoveFirst
  End If
  
  Do While True
      If rs_new.EOF Then
          rs_new.AddNew
          FN_new = FN
          rs_new.Update
          Exit Do
      End If
      If IsNull(FN_new.Value) Then
          rs_new.Edit
          FN_new = FN
          rs_new.Update
          Exit Do
      End If
      rs_new.MoveNext
  Loop
  rs_new.Close
  End Function
  
  Function CheckAge(Age As Field) As Boolean
  
  Dim d As Database
  Dim rs_new As Recordset
  Dim Age_new As Field
  
  Set d = CurrentDb()
  Set rs_new = d.OpenRecordset("Table4")
  Set Age_new = rs_new.Fields("Age")
  
  CheckAge = False
  
  Do While Not rs_new.EOF
      If Age_new = Age Then
          CheckAge = True
          Exit Do
      End If
      rs_new.MoveNext
  Loop
  
  rs_new.Close
  
  End Function
  
  Function WriteAge(Age As Field)
  Dim d As Database
  Dim rs_new As Recordset
  Dim Age_new As Field
  
  Set d = CurrentDb()
  Set rs_new = d.OpenRecordset("Table4")
  Set Age_new = rs_new.Fields("Age")
  
  If Not rs_new.EOF Then
      rs_new.MoveFirst
  End If
  
  Do While True
      If rs_new.EOF Then
          rs_new.AddNew
          Age_new = Age
          rs_new.Update
          Exit Do
      End If
      If IsNull(Age_new.Value) Then
          rs_new.Edit
          Age_new = Age
          rs_new.Update
          Exit Do
      End If
      rs_new.MoveNext
  Loop
  rs_new.Close
  End Function
  
  Function CheckSex(Sex As Field) As Boolean
  
  Dim d As Database
  Dim rs_new As Recordset
  Dim Sex_new As Field
  
  Set d = CurrentDb()
  Set rs_new = d.OpenRecordset("Table4")
  Set Sex_new = rs_new.Fields("Sex")
  
  CheckSex = False
  
  Do While Not rs_new.EOF
      If Sex_new = Sex Then
          CheckSex = True
          Exit Do
      End If
      rs_new.MoveNext
  Loop
  
  rs_new.Close
  
  End Function
  
  Function WriteSex(Sex As Field)
  Dim d As Database
  Dim rs_new As Recordset
  Dim Sex_new As Field
  
  Set d = CurrentDb()
  Set rs_new = d.OpenRecordset("Table4")
  Set Sex_new = rs_new.Fields("Sex")
  
  If Not rs_new.EOF Then
      rs_new.MoveFirst
  End If
  
  Do While True
      If rs_new.EOF Then
          rs_new.AddNew
          Sex_new = Sex
          rs_new.Update
          Exit Do
      End If
      If IsNull(Sex_new.Value) Then
          rs_new.Edit
          Sex_new = Sex
          rs_new.Update
          Exit Do
      End If
      rs_new.MoveNext
  Loop
  rs_new.Close
  End Function
  </code></pre>