Newbee - how to loop through table and delete unwanted records

[Newbee Adam]

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

 

[Nikos Yannacopoulos]

Adam,

How about making a new table with just the records that you want, and
then (renaming or) deleting the original table and renaming the new one
to the original name?making the new table is a piece of cake: start by
making a select query on the existing table, go View > Totals and notice
the new line headed Totals: that appears in the grid; the default
setting is Group By on all fields, leave it as is on the GroupNo field,
and set it to First for all other fields. Go to datasheet view to check
if you are getting what you wanted (which I expect you will). When happy
with it, just change the query type top Make Table (from the Query
menu), run it and you're done with making the new table.

HTH,
Nikos

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

 

[Newbee Adam]

I wish I would have read your response sooner, becuase I new there was a
simpler way. I went ahead and wrote this code that served the same purpose:

If Not rs.EOF Then rs.MoveFirst
Do While Not rs.EOF
'Counter = Counter + 1
RecordPos = (rs.RecordCount - (rs.RecordCount - Counter))

If Counter = 1 Then
PreviousLoopsGroupNo = rs!Number
Else
strName =
rs!Name ' here for debugging
ThisLoopsGroupNo = rs!Number

If ThisLoopsGroupNo = PreviousLoopsGroupNo Then
rs.Delete

Counter = Counter - 1 'Need this for accurate recordCount
Else
PreviousLoopsGroupNo = ThisLoopsGroupNo 'If all Ok then
we need a New First Group Number
End If
End If
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

Adam,

How about making a new table with just the records that you want, and
then (renaming or) deleting the original table and renaming the new one
to the original name?making the new table is a piece of cake: start by
making a select query on the existing table, go View > Totals and notice
the new line headed Totals: that appears in the grid; the default
setting is Group By on all fields, leave it as is on the GroupNo field,
and set it to First for all other fields. Go to datasheet view to check
if you are getting what you wanted (which I expect you will). When happy
with it, just change the query type top Make Table (from the Query
menu), run it and you're done with making the new table.

HTH,
Nikos

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