need advice on recordset table acess

Hi list,
Need the advice from you all.
when user input item ABC, logic will check if division for the itemnumber ABC has another same division value, if yes, logic will delete the duplicate SAME division where the itemnumber is NOT ABC.
Any help how to code this?

i know only need to have these but do not know how to search through the table to check and delete from bof until eof. compared and if found same division but different itemnumber then delete
Dim xx As ADODB.Recordset
Set xx = New ADODB.Recordset

With xxl
.ActiveConnection = cnsql
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "Select * from table where .....
End With

the total number of records of the table is more than 20k.
division itemnumber
4000 ABC
4000 XYZ (to be deleted)
3840 GHY
4567 PER
5000 ABC
5000 PHJ (to be deleted)
2450 ABC
2450 AQW (to be deleted)
7000 UJI
1740 ABC
1740 WUK (to be deleted)

the records that will be deleted are
4000 XYZ
5000 PHJ
2450 AQW
1740 WUK

thank you very much

Delete from Table where Itemnumber = 'xxxx'

Hi Randem,
You have got my problem wrong.
Anyway, thanks for reply.
Rgds

Then Just Select the division number with 'ABC' as the item number if you get a hit then just execute the statement

Delete from Table where division = nnn and itemnumber <> "ABC"

hi list,
any help please.
thanks
regards

Hi tango_ben

There may be lots of different ways to tackle this but
here is one that has worked for me. It's a two part process.
In the first step you determine what has to be deleted and
in the second step you perform the deletion.

You will need to open a recordset and a regular connection
to the database to perfrom the action query.

The recordset query determines what needs to be deleted.

VB Code:

SELECT DIVISION, count(DIVISION) as myCount FROM TEST_TABLE GROUP BY DIVISION

With this recordset you are interested in cases where myCount > 1

So you next loop through the recordset and execute a delete query against the
table ....

VB Code:

While yourRecordset.eof = false   
   If YourRecordset("myCount") > 1 then     
      mySQL = "DELETE FROM TEST_TABLE WHERE DIVISION = " + recordset("DIVISION") + " AND ITEMNUMBER <> 'ABC'"                               
      yourDBConnection.execute (mySQL)
   end if   
   yourRecordSet.MoveNext 
loop  
 
yourRecordset.close 
yourDBConnection.close

This should do it for you. Hope it helps you out.

here's another way ..

EDIT: updated so that if you have multiple duplicates it deletes them. Previous way only worked on 1 duplicate value.

VB Code:

Option Explicit
 
'// DECLARE ADO (MUST REFERENCE)
Private objConn As ADODB.Connection
Private objRs As ADODB.Recordset
 
'// NEW DATABASE CONNECTION
Private Sub OpenConnection()
    Set objConn = New ADODB.Connection                  ' SET NEW OBJECT VARIABLE
    ' OPEN DATABASE FILE
    objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myFile.Mdb"
End Sub
 
'// CLOSE CONNECTION
Private Sub CloseConnection()
    objConn.Close                                       ' CLOSE CONNECTION
    Set objConn = Nothing                               ' DESTROY VARIABLE
End Sub
        
'// CLOSE RECORDSET
Private Sub CloseRecordset()
    objRs.Close                                         ' CLOSE RECORDSET
    Set objRs = Nothing                                 ' DESTROY VARIABLE
End Sub
 
'// TEST BUTTON
Private Sub Command1_Click()
    
    ' DECLARATIONS
    Dim ArrData() As Variant                            ' TABLE ROWS HELD IN THIS ARRAY
    Dim strSQL As String
    Dim strTmp As String                                ' TEMP STRING USED IN LOOP
    Dim x As Long
    
    OpenConnection                                      ' OPEN CONNECTION
    strSQL = "SELECT division, itemnumber FROM test"    ' SQL QUERY
    Set objRs = objConn.Execute(strSQL, , adCmdText)    ' OPEN RECORDSET
    ArrData = objRs.GetRows()                           ' GET ALL ROW DATA INTO ARRAY
    CloseRecordset                                      ' CLOSE RECORDSET
    For x = 0 To UBound(ArrData, 2)                     ' LOOP THROUGH ROW ARRAY
        'ARRAY (0) = DIVISION, ARRAY (1) = ITEMNUMBER
        If strTmp = ArrData(0, x) Then                  ' PREV DIVISION VALUE WAS SAME
            ' DELETE THIS RECORD FROM THE DATABASE USING ITEMNUMBER VALUE
            objConn.Execute ("DELETE * FROM test WHERE itemnumber = '" & CStr(ArrData(1, x)) & "'")
        Else
            strTmp = ArrData(0, x)                      ' ASSIGN TMP VARIABLE AS DIVISION
        End If
    Next                                                ' LOOP TO NEXT RECORD
    CloseConnection                                     ' CLOSE CONNECTION
End Sub

hi all,
thank you so much of the help but i still unable to resolve.
the problem now is i cannot select item = ABC, if i do so, it will not retrieve another record with the same division if there is any but different itemnumber. for user, he needs to supply the itemnumber but in the program what i need to do is to delete another record that itemnumber <> ABC but same division.
i cannot select by division as there are hundreds of the division and only the itemnumber that user know of.
my example in my first post.
thanks again

Hi again tango_ben

Given the table you used in post #1

DIVISION ITEMNUMBER
----------- ------------------
4000 ABC
4000 XYZ
3840 GHY
4567 PER
5000 ABC
5000 PHJ
2450 ABC
2450 AQW
7000 UJI
1740 ABC
1740 WUK

the process I described will return

DIVISION ITEMNUMBER
----------- ------------------
4000 ABC
3840 GHY
4567 PER
5000 ABC
2450 ABC
7000 UJI
1740 ABC

I don't fully understand what you're asking for in your last post. Perhaps you could use this table to illustrate what still needs to be done.

Hi LinXG and all,
Really appreciate if you can check if my syntax is correct. i doubt its correct. i unable to test the connection as i am at home.
Again, thank you LinXG.
thanks


With rsGroup
.ActiveConnection = cnsql
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "Select div, count(div) as MyCount from maintable group by div"
End With

With rsDelete
.ActiveConnection = cnsql
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "Select * from maintable where qty <> 0"
End With

With rsDelete
.MoveFirst
Do While rsDelete.EOF = False

If rsGroup("MyCount") = 2 Then
mysql = "delete from rsDelete where div =" & rsGroup("div") & " and itemnumber <> '" & userinputvar & "'"
End If
rsDelete.mysql
.MoveNext
Loop
end with

Hi tango_ben;

I don't have VB or Access at the moment so my suggestions here are untested.

I noticed that you prefer to use a recordset to handle the deletes. This is fine but would be quite slow if there are a lot of them to do.

VB Code:

.Open "Select * from maintable where qty <> 0"

Where did qty come from? It wasn't part of your original table.

VB Code:

If rsGroup("MyCount") = 2 Then

rsGroup("MyCount") > 1 allows for the possibility that there could be 3,4,5 or many records to be deleted. Maybe in your case there will only ever be two, but something to keep an eye on.

VB Code:

With rsGroup 
   .ActiveConnection = cnsql 
   .CursorType = adOpenKeyset 
   .LockType = adLockOptimistic 
   .Open "Select div, count(div) as MyCount from maintable group by div" 
End With
 
Do While rsGroup.EOF = False     'This is your outter loop  
   If rsGroup("MyCount") = 2 Then   
      mysql = "delete from maintable where div =" & rsGroup("div") & " and itemnumber <> '" & userinputvar & "'" 
      With rsDelete              'This corresponds to inner loop where deletes occur. 
        .ActiveConnection = cnsql         
        .CursorType = adOpenKeyset 
        .LockType = adLockOptimistic 
        .Open mysql                       
      End With 
 
     rsDelete.Close  
 
   End If  
 
Loop

The above code should work. It's basically the same as my original post, except that it uses a recordset instead of a simple connection. Take a look at rory's code if you'd like to experiment with using a connection. His code is pretty straight forward and easy to understand.

I hope this is helpful to you.

Quote:

---

Originally Posted by tango_ben

hi all,
thank you so much of the help but i still unable to resolve.
the problem now is i cannot select item = ABC, if i do so, it will not retrieve another record with the same division if there is any but different itemnumber. for user, he needs to supply the itemnumber but in the program what i need to do is to delete another record that itemnumber <> ABC but same division.
i cannot select by division as there are hundreds of the division and only the itemnumber that user know of.
my example in my first post.
thanks again

---

the code i posted deletes the record if the record's "division" field value is the same as the previous record's "division" field value.

Doesn't matter whether the "itemnumber" is ABC, DEF, or XYZ, if it's a duplicate it will be deleted.

tango_ben, I updated the code sample above with comments.

Rory

Hi List,
Thank you so much of your reply. I have learned alot.
May I ask for advice further.
1) For the rsGroup, I would like to select with more condition. Current select statement the record that returned not clean and it affects mysql string to return incorrect data.
I take the example data in my first post
4000 ABC
4000 XYZ (to be deleted)
3840 GHY
4567 PER
5000 ABC
5000 PHJ (to be deleted)
2450 ABC
2450 AQW (to be deleted)
7000 UJI
1740 ABC
1740 WUK (to be deleted)

So in the rsGroup, the data should only have
4000 XYZ
5000 PHJ
2450 AQW
1740 WUK
Possible to add further condition in the select statement?
when user key in ABC itemnumber then the select statement should select the same div as ABC, if there is any, but itemnumber <> ABC.

With rsGroup
.ActiveConnection = cnsql
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "Select div, count(div) as MyCount from table where qty > 0 group by div having count(div) = 2"
End With

Do While rsGroup.EOF = False
If rsGroup("MyCount") = 2 Then
mysql = "select * from table where div =" & rsGroup("div") & " and itemnumber <> '" & SearchStr1 & "'"
With rsDel
.ActiveConnection = cnsql
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open mysql
End With

With rsDel
If rsDel.RecordCount = 1 Then
rsLogging.AddNew
rsLogging!div = .Fields(LTrim("div"))
rslogging!Itemnumber = .Fields("itemnumber")
rslogging.Update
.Delete
End If
End With
End If
Loop

2) when .open mysql it means delete? if i want to keep the delete record in a log file where should i put right before the delete?

3)Rory, really appreciate of your help. I not really understand your coding. it may too high level for me. i will learn your code later.

again thanks to all

ive attached a working sample with a Test Access Database ...

Hi Rory,
You are so helpful. I need to study your code as I still new to VB. Really appreciate your help.
Have you seen my latest post?
what if having the below records in the table. both item not ABC but div same.
your logic will also delete the second 1 as no checking the item.
what i need is when user enter the item, for example ABC, then i will delete the same div where item NOT ABC.

div item
3000 PED
3000 SWQ

thanks alot

Where the Sub Declarations are ..

VB Code:

Dim sItem As String

Where the Delete Command is in the loop...

VB Code:

sItem = ArrData(1, x)
If sItem <> "ABC" Then
    objConn.Execute ("DELETE * FROM test WHERE itemnumber = '" & sItem & "'")
End If

Quote:

---

Originally Posted by tango_ben

Hi Rory,
You are so helpful. I need to study your code as I still new to VB. Really appreciate your help.
Have you seen my latest post?
what if having the below records in the table. both item not ABC but div same.
your logic will also delete the second 1 as no checking the item.
what i need is when user enter the item, for example ABC, then i will delete the same div where item NOT ABC.

div item
3000 PED
3000 SWQ

thanks alot

---

Sorry, do you need to check for the value "ABC" for some reason?
Do you only want to delete a record if the previous one was ABC and is a duplicate?

VB Code:

Dim ArrData() As Variant                            ' TABLE ROWS HELD IN THIS ARRAY
    Dim strSQL As String
    Dim strTmp As String                                ' TEMP STRING USED IN LOOP
    Dim strRet As String
    Dim x As Long
    
    OpenConnection                                      ' OPEN CONNECTION
    strSQL = "SELECT division, itemnumber FROM test"    ' SQL QUERY
    Set objRs = objConn.Execute(strSQL, , adCmdText)    ' OPEN RECORDSET
    ArrData = objRs.GetRows()                           ' GET ALL ROW DATA INTO ARRAY
    CloseRecordset                                      ' CLOSE RECORDSET
    For x = 0 To UBound(ArrData, 2)                     ' LOOP THROUGH ROW ARRAY
        'ARRAY (0) = DIVISION, ARRAY (1) = ITEMNUMBER
        If strTmp = ArrData(0, x) And strRet = "ABC" Then        
            ' DELETE THIS RECORD FROM THE DATABASE USING ITEMNUMBER VALUE
            objConn.Execute ("DELETE * FROM test WHERE itemnumber = '" & CStr(ArrData(1, x)) & "'")
        Else
            strTmp = ArrData(0, x)                      ' ASSIGN TMP VARIABLE AS DIVISION
            strRet = ArrData(1, x)
        End If
    Next

Hi Rory,
Yes. User will input the itemnumber and i will check the itemnumber. If the itemnumber is the one user input then will check if another record having the same div, if yes then will delete.

5000, ABC not to be deleted as no other record with the same div

div item
2000 ABC
2000 XRS (to be deleted)
4500 QAW (to be deleted)
4500 ABC
3450 PQS
7810 QAW
5000 ABC

thanks