Removing Duplicate Records. (1 Viewer)

[Extra_Cover]

Hi All - New here.

You look like a knowledgeable lot and I wonder if anyone has a better way of solving a problem I have?

I can Join a table imported from a telephone management system to a table containing customer information by the 'phone number field. However the customer information table contains duplicate telephone numbers (each customer has a code that starts with a T and a G - it's a long story...). It doesn't matter which code is used.

The code I have used follows, it basically read all the records in to an array and then loops through a replica record set, deletes any occurrences of the number and adds the array value back. While the code works I'm sure there is a cleaner (and faster - their are over 8000 customers to trawl through) way to do this.

' Next Remove any duplicate Numbers

'Set rs = CurrentDb.OpenRecordset("SELECT * FROM TblNoSpace")
'Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM TblNoSpaceFinal")
'With rs
' rs.MoveFirst
' x = 1
' Do Until rs.EOF
' custcode(x) = rs.Fields("lcus_code")
' custname(x) = rs.Fields("lcus_name")
' custphone(x) = rs.Fields("lcus_phone")
' rs.MoveNext
' x = x + 1
' Loop
'End With
'MsgBox ("All REcords copied to Array " & x)

'For y = 1 To x
'With rs1
' rs1.MoveFirst
'
' Do Until rs1.EOF
' If rs1.Fields("lcus_phone") = custphone Then
' rs1.Delete
' End If
' rs1.MoveNext
'
' Loop
' Add Record Back In
' rs1.AddNew
' rs1.Fields("lcus_code") = custcode
' rs1.Fields("lcus_name") = custname
' rs1.Fields("lcus_phone") = custphone
' rs1.Update
' End With
' Me.FrmRecCount1 = y
'Next y
'rs.Close
'rs1.Close
'Set rs = Nothing
'Set rs1 = Nothing

 

[Mutdogus]

If the only difference between the records is a T or a G at the beginning of the one phone number field, and it's always only 1 letter...

Wrap the Phone number field in a query with right([lcus_phone],10) if you are in the US.

Then group the query by phone number. You could make the query append all the data to a new table and use this instead of your "infected" table.

I think I must be missing something, that is a lot of code for something that seems like it can be done with one line in a query.

Also.. are you just doing a one time clean-up of the table? Or are you going to have to constantly clear this table.

 

[nIGHTmAYOR]

How i read the code is that his problem lays where he gets repeated identicle phone numbers yet the index (lcus_code) varies..and thats how he gets repetitions.

now the question is .. why is he allowing the error then trying to clean it ? one simple strike shot to the case would be setting the field lcus_phone Index property to Yes (No Duplicates) in table design and then adding an error control to cover record violations (repeated entries).

 

[Extra_Cover]

Unfortunately, Mut, it doesn't follow that code GABC1 corresponds to TABC1.

nIGHT: The lcus_code data is gained from an ODBC source and, while a clean up of this data would be recommend, I cannot delete duplicate accounts as this compromises data models that look back over 6 years of sales history. Only one customer code is now used but I would have to wait 4 years for all 'old' data to be archived!

I think I may have found a smarter solution. Running my code at installation will create the table of data I want. Then running an unmatched query each evening on the telephone number would return any new customers created. These I could add to the table.

Thanks for responses.

 

[Mutdogus]

So ABC1 would be the lcus_code that you want? Just build a query off that table and get rid of that T or G and group the results. Base your join off of that query.