Finding duplicates w/sql

[Dubya007]

how does one find duplicates with SQL?

[szlamany]

If I understand your question - I just answered a similar one yesterday.

See this thread: http://www.vbforums.com/showthread.p...hreadid=293577

[Dubya007]

i read the thread but i'm not sure how that would work for me, i have a table of which has some machine numbers and it also has more than one date entry for each machine. in the table veiw of access97 there is more than one entry for each machine 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.

[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.

[Dubya007]

i adapted your code, but it does not work. i have posted it below, maybe you can look @ it and tell me where i am going wrong. i didn't use the
rs!.... because i didn't understand it, maybe that is where i am going wrong. here is the code

Private Sub Form_Load()
MachID = Trim(txtID.Text)
Debug.Print MachID
Dim sql As String
sql = "SELECT IDNum, COUNT(IDNum) FROM Testble GROUP BY IDNum  HAVING COUNT(*)>1"
 
If rs.State = adStateClosed Then
    rs.Open sql, DBConn, adOpenDynamic, adLockBatchOptimistic, adCmdText
    'Display function just displays the Machine numnber in a text box for now _
    it will be used to display other things later.
    Call display(0)
Else
    Call display(0)
End If
 
If rs.RecordCount > 0 Then
    rs.MoveFirst
    
    Do While Not rs.EOF
        Call FillList(Trim(rs.Fields("IDNum")))
        rs.MoveNext
        Loop
    End If
End Sub
Private Sub FillList(ByVal IDNum As String)
Dim trs As New ADODB.Recordset
Dim sql As String
 
 
sql = "SELECT Date FROM Testble " _
    & "WHERE IDNum = '" & Trim(IDNum) & "' ORDER BY Date ASC"
 
 
trs.Open sql, DBConn, adOpenKeyset, adLockOptimistic, adCmdText
 
If trs.RecordCount > 0 Then
    trs.MoveFirst
    Do While Not trs.EOF
        cmbDates.AddItem trs.Fields("Date").Value
        trs.MoveNext
Loop
End If
End Sub

[Dubya007]

oh i almost forgot to tell you that i don't get an error, it finds all the machines that are in there more than once but it doesn't fill the combo box with the corrosponding dates. the combo box is just empty

[techgnome]

put a break point on this line:

If trs.RecordCount > 0 Then

I'd bet that .RecordCount returns -1. Try this instead:

If not (trs.EOF And trs.BOF) Then
    trs.MoveFirst
    Do While Not trs.EOF
        cmbDates.AddItem trs.Fields("Date").Value
        trs.MoveNext
Loop
End If

TG

[Dubya007]

that didn't work either. i did some testing and the FillList sub is never even being called! so i have to figure out why this is.

[Wen Lie]

that didn't work either. i did some testing and the FillList sub is never even being called! so i have to figure out why this is.

no need to be panic.....
pls take a look at your coding again. I'll show you why FillList sub is never being called....

in your code :

sql = "SELECT IDNum, COUNT(IDNum) FROM Testble GROUP BY IDNum  HAVING COUNT(*)>1"
 
If rs.State = adStateClosed Then
    rs.Open sql, DBConn, adOpenDynamic, adLockBatchOptimistic, adCmdText
    'Display function just displays the Machine numnber in a text box for now _
    it will be used to display other things later.
    Call display(0)
Else
    Call display(0)
End If
 
If rs.RecordCount > 0 Then
    rs.MoveFirst
    
    Do While Not rs.EOF
        Call FillList(Trim(rs.Fields("IDNum")))
        rs.MoveNext
        Loop
    End If
End Sub

please note that you open the recordset by using adOpenDynamic. And .recordcount method can only be used if you open recordset by using adOpenKeyset.

If open method <> adOpenKeyset, then .RecordCount will return -1

If you open by adOpenDynamic, you can try to use :

If rs.bof <> true and rs.eof <> true Then
.....
else
....
end if

try it, I hope your FillList sub will be called

[Ecniv]

Originally posted by Wen Lie
please note that you open the recordset by using adOpenDynamic. And .recordcount method can only be used if you open recordset by using adOpenKeyset.

Um, I use it with static (snapshot) of the table, and recordcount works with that, for you info

Personally I use:
- dynamic - updated/editting
- static - read only - filling list/combo boxes


Vince

[Dubya007]

i actually was able to get it to workwith out having any if statements(if this is bad let me know) but here is my code the problem now is now that i want to be able to click on one of the dates that is in the box. the problem is that no matter what i use for the SQL statement says that i have a syntax error but i can't find it.

Private Sub lstDates_Click()
Dim cmdCommand As New ADODB.Command
Dim sql As String
Dim trs As New ADODB.Recordset
 
Set cmdCommand.ActiveConnection = DBConn
cmdCommand.CommandType = adCmdText
 
sql = "SELECT DISTINCT Testble.IDNum, tblMachines.machine_desc, Testble.NeedACheck," _
       & "tblMachines.NeedsCheck, Testble.Date, tblMachines.deptId, Testble.Supervisor " _
       & "FROM tblMachines, Testble WHERE tblMachines.ID = Testble.IDNum AND (Testble.Date = '" & lstDates.Text '")
 
 
cmdCommand.CommandText = sql
 
Set trs = cmdCommand.Execute
Unload Me
frmSafeGuard.Show
End Sub

[Dubya007]

i fixed the syntax error but the problem is that it will only show the data for the first date entry. for example
if i have a machine with the number of 8
and it has the entries
3/6/2004
3/7/2004
3/12/2004
3/13/2004
3/14/2004

and i click on 3/7/2004
it will show the entry for 3/6/2004
here is my SQL statment

sql = "SELECT DISTINCT Testble.IDNum, tblMachines.machine_desc, Testble.NeedACheck," _
       & "tblMachines.NeedsCheck, Testble.Date, tblMachines.deptId, Testble.Supervisor " _
       & "FROM tblMachines, Testble WHERE tblMachines.ID = Testble.IDNum AND Testble.[Date] = " & lstDates.Text

[Wen Lie]

Originally posted by Ecniv
Um, I use it with static (snapshot) of the table, and recordcount works with that, for you info

Personally I use:
- dynamic - updated/editting
- static - read only - filling list/combo boxes


Vince

huehuehue... thx for the correction dude. I forgot. And just remember when you tell me

I use static sometimes (rarely), nd works also with .recordcount method ;p~

I just remember ;p nd thx to remind me bout this ;p~
but for dynamic, .recordcount never works.

[Wen Lie]

Originally posted by Dubya007
.......
3/6/2004
3/7/2004
3/12/2004
3/13/2004
3/14/2004

and i click on 3/7/2004
it will show the entry for 3/6/2004
here is my SQL statment

sql = "SELECT DISTINCT Testble.IDNum, tblMachines.machine_desc, Testble.NeedACheck," _
       & "tblMachines.NeedsCheck, Testble.Date, tblMachines.deptId, Testble.Supervisor " _
       & "FROM tblMachines, Testble WHERE tblMachines.ID = Testble.IDNum AND Testble.[Date] = " & lstDates.Text

it should be 3/7/2004 as the output ???
for your T-SQL, try below :

sql = "SELECT DISTINCT Testble.IDNum, tblMachines.machine_desc" _
& ", Testble.NeedACheck, tblMachines.NeedsCheck, Testble.Date" _
& ", tblMachines.deptId, Testble.Supervisor FROM tblMachines" _
& ", Testble WHERE tblMachines.ID = Testble.IDNum AND Testble.[Date] = '" _
& lstDates.Text & "' and tblMachines.ID = 8"

if tblMachines.ID is a float data type, and suppose your ID input is txtMachineID then try this :

sql = "SELECT DISTINCT Testble.IDNum, tblMachines.machine_desc" _
& ", Testble.NeedACheck, tblMachines.NeedsCheck, Testble.Date" _
& ", tblMachines.deptId, Testble.Supervisor FROM tblMachines" _
& ", Testble WHERE tblMachines.ID = Testble.IDNum AND Testble.[Date] = '" _
& lstDates.Text & "' and tblMachines.ID = " & val(txtMachineID.Text) & ""

at your given query to us, seems you've missed the machine ID. Please take a look at your where clause. You only set tblMachines.ID = Testble.IDNum....
but you didn't specify which machine ID you need to query.

hope that's what you're trying to fix

[Ecniv]

Originally posted by Dubya007
i fixed the syntax error but the problem is that it will only show the data for the first date entry. for example
if i have a machine with the number of 8
and it has the entries
3/6/2004
3/7/2004
3/12/2004
3/13/2004
3/14/2004

and i click on 3/7/2004
it will show the entry for 3/6/2004
here is my SQL statment

sql = "SELECT DISTINCT Testble.IDNum, tblMachines.machine_desc, Testble.NeedACheck," _
       & "tblMachines.NeedsCheck, Testble.Date, tblMachines.deptId, Testble.Supervisor " _
       & "FROM tblMachines, Testble WHERE tblMachines.ID = Testble.IDNum AND Testble.[Date] = " & lstDates.Text

The bit in bold suggests that the code ID you use to pull the data onto the form is offset. combo lists start at 0, but if you are using the listindex, it should point to the correct place.

strSql="SELECT DISTINCT Testble.IDNum, tblMachines.machine_desc, Testble.NeedACheck, tblMachines.NeedsCheck, Testble.Date, tblMachines.deptId, Testble.Supervisor "
strsql=strsql & "FROM tblMachines, Testble "
strsql=strsql & "WHERE tblMachines.ID = Testble.IDNum AND Testble.[Date] = #" & format(cdate(lstDates.list(lstDates.listindex,0)),"dd mmm yyyy") & "#"

Sql is in american format, so uk dates may be off. If it accepts the above, it should convert to a date reformat it to a nicer, non confusing date.

If there is an id for a particular record, this should be held in the combo, making the combo a two column list, the first column being Zero. I don't know if this is required, because if you have duplicate records for the date, then an id is not required.

Was the db in Sql Server or Oracle? (just wondering on the join)

Hope its going ok.


Vince
EDIT:> Just noticed you are from us... so forget the reformatting of the date function

[Dubya007]

thanx for all the help i am going to try this stuff later, when i get a chance, i'll let you know how it goes.