Modify duplicate records using VBA code

Re: Modify duplicate records using VBA code

On Mon, 30 Jun 2008 08:37:11 -0700 (PDT), "Brett Barry: Go Get Geek!"
<brettcbarry@gm ail.comwrote:
I'm not sure you're gonna get much help
from this forum on this request. Your key
statement is...

"...the program I am importing into doesn't
allow duplicate customer names..."

I think that program you're using is trying
to tell you something. There are lot's of
ways to append additional string data
onto strings. Getting the sequential integers
appended onto the ends of your customer
names isn't gonna be difficult. Recovering
from the mess made by doing so will be.

Re: Modify duplicate records using VBA code

This is what I want to do.

Re: Modify duplicate records using VBA code

>xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxx

OK - Is it a one-time thing? That is,
once you do it, you won't have to
do it again. And, does it matter what
the additional unique identifiers are
that you tack on to the end of the
customer name strings? And last,
do all the records containing cust
name strings have unique index
field values?

Assuming the answers are Yes,
No and Yes respectively. Extract
duped records, outputting only
the [UniqueID] and [CustName]
fields. Using a MakeTable qry
to create the target table would
make this easier. Call it NewTable.
Open NewTable in Design view.
Change the field type of [UniqueID]
to LongInteger if it's AutoNumber.
Add a new field named [ID] to the
table - AutoNumber type. Save the
table. Create a query based on
NewTable, outputting all fields.
Put a calculated field in the query
named [NewCustName] like this...
NewCustName: [CustName] & Trim$(CStr([CustName]))

Run the query. There are your
new customer names. Paste 'em
into a new test copy of your table,
overwriting the previous redundant
values with the new, unique values.

Re: Modify duplicate records using VBA code

I'm not sure if this is related, but I would prefer VBA code that
loops through and for each duplicate of the same name, add a number in
sequence, similar to this code that deletes the duplicates:


1.
Function DeleteDuplicate s_Click()
2.
On Error Resume Next
3.

4.
Dim db As Database, rst As Recordset
5.
Dim strDupName As String, strSaveName As String
6.
Dim strSQL As String
7.
strSQL = "SELECT [JOBNUM], [trnd] FROM Tableqryduplica tes"
8.

9.
Set db = CurrentDb()
10.
Set rst = db.openrecordse t(strSQL, dbOpenSnapshot)
11.

12.

13.
If rst.BOF And rst.EOF Then
14.
MsgBox "No records to process"
15.
Else
16.
rst.MoveFirst
17.
Do Until rst.EOF
18.
strDupName = rst.Fields("JOB NUM") & rst.Fields("trn d")
19.

20.
If strDupName = strSaveName Then
21.
rst.Delete
22.
Else
23.
strSaveName = rst.Fields("JOB NUM") & rst.Fields("trn d")
24.
End If
25.
rst.MoveNext
26.
Loop
27.

28.
rst.Close
29.
Set rst = Nothing
30.
Set db = Nothing
31.
End If
32.

33.

34.
End Function

Re: Modify duplicate records using VBA code

Can't help you there. Maybe someone
else will jump in.