Reply to thread

Message

<blockquote data-quote="Newbee Adam" data-source="post: 1930928">My table has records in it that are not exact duplicates but&nbsp; I only want 1record per group number. Note it is always the 1st record of the new groupNoI want.example:GroupNo&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; GroupName_and_Location&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Termination Date1201&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; GBKI Inc200801011201&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; GBKI Inc - Houston200801011201&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; GBKI Inc - Chicago20080101I tried several differentI tried several complex querys from several peoples suggestion with nosuccess. So I have decided to try to loop though the table and compare thegroupNo value to the groupNo value in the previos record I just loopedthrough. If it is the same ,then delte this current record I am on and an goto next loop and compare, else just loop to next record. This is my first timeto loop through records like thius. Can I do this and if so , how do I tellit to delete the current record. As you can see My plan now is to:1.make and SQL string : SQL = &quot;DELETE * FROM GroupLookUp WHERE GroupNo '&quot; &amp;&quot;Delete Me!&quot; &amp; &quot;';&quot;2. When I loop through a record if the group value is the same as theprevious record,I set the value of the current record's field of GroupNo (which is a string)to &quot;Delete Me!&quot; (I have not run the code this far I am sure I will get anerror on that line, I am not sure if you can change a field value without ansql statement...or can I ??)3. Then the next line of code runs the SQL delete string I made in step 1.SQL = &quot;DELETE * FROM GroupLookUp WHERE [gmgpno] = '&quot; &amp; &quot;Delete Me!&quot; &amp; &quot;'&quot;'If I cannot do above SQL then try something like this: &quot;DELETE * FROMGroupLookUp WHERE RecordPos = Counter&quot;Do Until rs.EOFCounter = Counter + 1RecordPos = (rs.RecordCount - (rs.RecordCount - Counter))If Counter = 1 ThenPreviousLoopGroupNo = [gmgpno]ElseThisLoopsGroupNo = [gmgpno]If ThisLoopsGroupNo = PreviousLoopsGroupNo Then[gmgpno] = &quot;Delete Me!&quot;DoCmd.RunSQL (SQL)Counter = Counter - 1 'Need this for accurate recordCountElsePreviousLoopsGroupNo = ThisLoopsGroupNoEnd IfEnd IfLoopEnd Sub</blockquote>

[QUOTE="Newbee Adam, post: 1930928"] 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 [/QUOTE]

Verification