Newbee - how to loop through table and delete unwanted records

From: Newbee Adam (NewbeeAdam_at_discussions.microsoft.com)
Date: 03/07/05 

Date: Mon, 7 Mar 2005 06:59:03 -0800

My table has records in it that are not exact duplicates but I only want 1
record per group number. Note it is always the 1st record of the new groupNo
I want.

example:
GroupNo GroupName_and_Location Termination Date

1201 GBKI Inc
20080101
1201 GBKI Inc - Houston
20080101
1201 GBKI Inc - Chicago
20080101

I tried several different
I tried several complex querys from several peoples suggestion with no
success. So I have decided to try to loop though the table and compare the
groupNo value to the groupNo value in the previos record I just looped
through. If it is the same ,then delte this current record I am on and an go
to next loop and compare, else just loop to next record. This is my first tim
eto loop through records like thius. Can I do this and if so , how do I tell
it to delete the current record. As you can see My plan now is to:
1.make and SQL string : SQL = "DELETE * FROM GroupLookUp WHERE GroupNo '" &
"Delete Me!" & "';"
2. When I loop through a record if the group value is the same as the
previous record,
I set the value of the current record's field of GroupNo (which is a string)
to "Delete Me!" (I have not run the code this far I am sure I will get an
error on that line, I am not sure if you can change a field value without an
sql statement...or can I ??)
3. Then the next line of code runs the SQL delete string I made in step 1.
SQL = "DELETE * FROM GroupLookUp WHERE [gmgpno] = '" & "Delete Me!" & "'"
'If I cannot do above SQL then try something like this: "DELETE * FROM
GroupLookUp WHERE RecordPos = Counter"

Do Until rs.EOF
    Counter = Counter + 1
    RecordPos = (rs.RecordCount - (rs.RecordCount - Counter))
    
    If Counter = 1 Then
        PreviousLoopGroupNo = [gmgpno]
    Else
        
        ThisLoopsGroupNo = [gmgpno]
            
            If ThisLoopsGroupNo = PreviousLoopsGroupNo Then
                [gmgpno] = "Delete Me!"
                DoCmd.RunSQL (SQL)
                Counter = Counter - 1 'Need this for accurate recordCount
            Else
                PreviousLoopsGroupNo = ThisLoopsGroupNo
            End If
    End If
    
Loop

End Sub 

-- 
Adam S

Relevant Pages

  • Re: Dynamic SQL Execution
    ... the string: ... > @rOUT OUTPUT ... > SQL Server MVP ... >> In a cursor, as I loop through, I create a dynamic SQL each time. ...
    (microsoft.public.sqlserver.programming)
  • Re: Slow performance with SQL and stmt.executeUpdate()
    ... method call, using SQL insert. ... Statement stmt = conn.createStatement; ... String phone = md.getPhone; ... after the loop is finished. ...
    (comp.lang.java.databases)
  • Re: Can you use dynamic data in a request form
    ... I am am trying to write some code to perform a INSERT in SQL ... > table using a loop. ... How that string is built is irrelevant. ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.general)
  • Re: Loop through names of controls
    ... Didn't scroll down to the string of the sql. ... thought he was having trouble getting an object in his loop. ... >> I'm running some SQL to insert the value in each control into a table. ...
    (microsoft.public.access.formscoding)
  • extension_pack
    ... It is used to set upper loop -- limits for non-deterministic values thus avoiding the use of access -- types and enabling the functions to be used for synthesizeable code. ... DivisorVal: integer) return std_logic_vector; function "/"(DividendVal: string; DivisorVal: integer) return std_logic_vector; ... for loopVar in 0 to slvVal'length/4-1 loop ... end loop; if then return not resultVar; -- "width mismatch" errors here are due to improper sizing of the vector that this function is assigned to else return resultVar; -- "width mismatch" errors here are due to improper sizing of the vector that this function is assigned to end if; ...
    (comp.lang.vhdl)