Modify duplicate records using VBA code

[Brett Barry: Go Get Geek!]

Hello,

Can someone please paste VBA code and how I would go about doing
this?:

I have a customer table with over 6000 duplicates in the field called
"Customer". While the customer names may be duplicates the data in
other fields is different. I would like to import all of the customers
including the duplicates; however, the program I am importing into
doesn't allow duplicate customer names.

Therefore, my goal is to loop through the table using VBA, if
possible, and for each duplicate in the Customer field, add a number
or character.

For example:

John Doe1
John Doe2
John Doe3

Jane Smith1
Jane Smith2

If you can provide instructions on how to create and run the VBA code,
that would be appreciated.

Thanks,
Brett

[MLH]

On Mon, 30 Jun 2008 08:37:11 -0700 (PDT), "Brett Barry: Go Get Geek!"
<br*********@gmail.comwrote:

>Hello,

Can someone please paste VBA code and how I would go about doing
this?:

I have a customer table with over 6000 duplicates in the field called
"Customer". While the customer names may be duplicates the data in
other fields is different. I would like to import all of the customers
including the duplicates; however, the program I am importing into
doesn't allow duplicate customer names.

Therefore, my goal is to loop through the table using VBA, if
possible, and for each duplicate in the Customer field, add a number
or character.

For example:

John Doe1
John Doe2
John Doe3

Jane Smith1
Jane Smith2

If you can provide instructions on how to create and run the VBA code,
that would be appreciated.

Thanks,
Brett

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.

[Brett Barry: Go Get Geek!]

Getting the sequential integers
appended onto the ends of your customer
names isn't gonna be difficult.

This is what I want to do.

[MLH]

>

>This is what I want to do.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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.

[Brett Barry: Go Get Geek!]

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 DeleteDuplicates_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 Tableqryduplicates"
8.

9.
Set db = CurrentDb()
10.
Set rst = db.openrecordset(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("JOBNUM") & rst.Fields("trnd")
19.

20.
If strDupName = strSaveName Then
21.
rst.Delete
22.
Else
23.
strSaveName = rst.Fields("JOBNUM") & rst.Fields("trnd")
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

[MLH]

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