delete duplicates with loop?

  • Thread starter Thread starter swngdncr
  • Start date Start date
S

swngdncr

Hello, I have a table that was imported from our GIS system that lists
all of the intersections in our city. However, every intersection is
listed twice, once where StName1 is X and StName2 is Y, then again in
reverse, StName1 is Y and StName2 is X. I need to delete the
duplicates, so that an intersection is only listed once. I'm sort of
stumped on this one, I'm thinking some kind of loop statement that
says if StName1 = StName2 AND StName2=StName1 then delete the one that
meets some criteria (possibly the one with lowest ObjectID). (BTW,
all the street names are exact, there is no problem with Street A
being called St. A etc.). Thanks in advance for your assistance.
 
I guess it doesn't matter which record is retained and which deleted?

Here's a thought.

Make sure you have an compound index of Street1 + Street2

Read a record and then Seek another record with the values reversed. That
is if the current record is StreetA + StreetB, seek StreetB + StreetA. If
you find it then delete it.

Do this from the bottom (last record) of the recordset to the top (first
record).

If you want more detailed help with the code then shout.

Rod
 
Hello, I have a table that was imported from our GIS system that lists
all of the intersections in our city. However, every intersection is
listed twice, once where StName1 is X and StName2 is Y, then again in
reverse, StName1 is Y and StName2 is X. I need to delete the
duplicates, so that an intersection is only listed once. I'm sort of
stumped on this one, I'm thinking some kind of loop statement that
says if StName1 = StName2 AND StName2=StName1 then delete the one that
meets some criteria (possibly the one with lowest ObjectID). (BTW,
all the street names are exact, there is no problem with Street A
being called St. A etc.). Thanks in advance for your assistance.
Click to expand...

ARRRRGH. I've done this with a query (or two) but can't remember how I did
it.
I'll dig it out tomorrow if nobody else has a solution.

In the meantime is that the only information in the table? Lat and Long
should be the same and you could run against that with just an index that
did not allow dupes.
 
Hello, I have a table that was imported from our GIS system that lists
all of the intersections in our city. However, every intersection is
listed twice, once where StName1 is X and StName2 is Y, then again in
reverse, StName1 is Y and StName2 is X. I need to delete the
duplicates, so that an intersection is only listed once. I'm sort of
stumped on this one, I'm thinking some kind of loop statement that
says if StName1 = StName2 AND StName2=StName1 then delete the one that
meets some criteria (possibly the one with lowest ObjectID). (BTW,
all the street names are exact, there is no problem with Street A
being called St. A etc.). Thanks in advance for your assistance.
Click to expand...


maybe

DELETE Intersections.*
FROM Intersections
WHERE EXISTS (SELECT *
FROM Intersections AS i
WHERE i.street1 = Intersections.street2
AND i.street2 = Intersections.street1
AND i.intersection_id < Intersections.intersection_id);
 
I'll give this a shot, probably can't work on it again until this
afternoon. Mike, if you find your code, I'd definately appreciate
it. I have already created two calculated fields that use the street
ID's to create Intersection and Intersection reverse... i.e. the
Intersetion of street 123 and street 456 is "123456" and
IntersectionReverse is "654321"...
 
yup, you are right re. the streets. OK, first.. understand that the
only training I have had in VB is from our former IT director who
helped me build this program, and taught me to code. I understand
enough to find code that is close to what I need, then to edit until
it works. So, I build up from what I know, getting different pieces
to work. I did a little form with a button on it, and put this code
behind the button. Seems to sucessfully find the duplicates and count
the number of duplicates, then move to the next record and do it
again, now I need to go to the next step and get it to delete one of
the two. Seems like I should be able to open a recordset of the two
duplicates, and tell it to go to first and delete it, but I haven't
found a way to make that work yet. OK, so here is my novice code:

Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim VarCrossOne As Variant
Dim VarCrossTwo As Variant
Dim strCntSQL As String
Dim rsDuplicateCnt As New ADODB.Recordset


Set db = CurrentDb

strSQL = "SELECT qryFindDuplicateIntersections.OBJECTID,
qryFindDuplicateIntersections.STNAME1,
qryFindDuplicateIntersections.STNAME2,
qryFindDuplicateIntersections.WhlName,
qryFindDuplicateIntersections.WhlNameReverse " & _
" FROM qryFindDuplicateIntersections"

Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
Recordset.MoveFirst

Do Until rs.EOF

VarCrossOne = Me.WhlName
VarCrossTwo = Me.WhlNameReverse

strCntSQL = " SELECT Count(*) AS Cnt" & _
" FROM qryFindDuplicateIntersections " & _
" WHERE qryFindDuplicateIntersections.WhlName = " &
VarCrossTwo & " AND qryFindDuplicateIntersections.WhlNameReverse = "
& VarCrossOne & ""

rsDuplicateCnt.Open strCntSQL, CurrentProject.Connection,
adOpenKeyset, adLockOptimistic

If rsDuplicateCnt.Fields("Cnt").Value > 0 Then

'MsgBox "Duplicate"
Else

End If

rsDuplicateCnt.Close
rs.Close

Recordset.MoveNext

Loop

rs.Close
Set rs = Nothing

Me.RecordSource = strSQL
Me.Requery
 
We're all making this far too complicated. Use the following or similar:

Dim rstIntersections As DAO.Recordset
Dim rstClone As DAO.Recordset
Dim dbs As Database

Set dbs = CurrentDb
Set rstIntersections = dbs.OpenRecordset("tblIntersections",
dbOpenDynaset)
Set rstClone = rstIntersections.Clone

rstIntersections.MoveLast
Do Until rstIntersections.BOF
rstClone.FindFirst "[Street1] = '" & rstIntersections![Street2] & "'
AND [Street2] = '" & rstIntersections![Street1] & "'"
If Not rstClone.NoMatch Then
rstIntersections.Delete
End If
rstIntersections.MovePrevious
Loop


Rod
 
Back
Top