Finding duplicates w/sql

[Wen Lie]

Originally posted by Dubya007
....what i am trying to get VB to do is to read in all the values for the machiines and then in a combo box put the date(s) that corrospond to that machine.

what values should VB read ? the entry date ?

from szlamany's t-sql :

SELECT THECOLUMN,SUM(1) FROM THETABLE GROUP BY THECOLUMN HAVING SUM(1)>1

change to :

SELECT machine_id,SUM(1) FROM machineTABLE GROUP BY machine_id HAVING SUM(1)>1

the query will list machine with more than one entry date. then you can use the ID to get the entry date and put in combo box.

private sub module_one()
   dim rs as new adodb.recordset
   'assume that myconn is your connection var
 
   rs.open "SELECT machine_id,SUM(1) FROM machineTABLE GROUP BY machine_id HAVING SUM(1)>1",myconn,adopenkeyset,adlockoptimistic,adcmdtext
 
   if rs.recordcount>0 then
      rs.movefirst
 
      do while not rs.eof
           call fill_combo(trim(rs!machine_id))
 
           rs.movenext
      loop
   end if
 
   rs.close
   set rs=nothing
end sub
 
private sub fill_combo(byval machine_id as string)
     dim rs2 as new adodb.recordset
 
      rs2.open "select entry_date from machineTABLE where machine_id = '" & trim(machine_id) & "' order by entry_date asc", myconn,adopenkeyset, adlockoptimistic, adcmdtext
 
      if rs2.recordcount > 0 then
           rs2.movefirst
           
           do while not rs2.eof
                'assume combobox name = Combo1
                combo1.additem format(rs2!entry_date, "mm/dd/yyyy")
 
                rs2.movenext
           loop
      end if
 
      rs2.close
      set rs2=nothing
end sub

do i make mistake here with the help ? i hope that's your point....
if not, pls clarify me / us here.