Q re procedure to chg field values

R

rasta

hi,

looking for an automated way to change the value of duplicate values in a
specifc fld. example:

<< BEFORE >>
Frank
Frank
Frank
Frank
Jack
Jack
Joe

<< AFTER >>
Frank
Frank2
Frank3
Frank4
Jack
Jack2
Joe

thank you.

r
 
R

Ron Weiner

Two Loops.

The outer loop iterates through the records of a query that finds all of the
rows that have duplicate values in the target column. The inner loop
iterates through the records whose value equals the value of the duplicate
column from the outer loop updating the column of each duplicate row
appropriately.

Ron W
www.WorksRite.com
 
R

Ron Weiner

rasta

It just occurred to me that it can be done in one with one loop by designing
a query that uses a sub query as it criteria. For example:

Select SpecifcCol as DupName
From SpecifcTable
Where SpecifcCol In
(Select SpecifcCol
From SpecifcTable
Group By SpecifcCol
Having Count(SpecifcCol)>1)
Order By WhateverMakesSense

Open this query in a recordset and iterate that recordset updating DupName
along the way. Here is some Pseudo Code that you should be able to convert
pretty easily into a working subroutine.

Open RS (using the Select statement above)
strOldName = ""
Do While not RS.EOF
if RS!DupName = strOldName then
strOldName = RS!DupName
RS!DupName = RS!DupName & i
RS.Update
i = i + 1
Else
strOldName = RS!DupName
i = 2
End If
RS.MoveNext
Loop
Close RS

Make a backup and test there before submitting your real data to this
potentially disastrous procedure. Obviously none of this has been tested.

Ron W
www.WorksRite.com
 
R

Randy Harris

Ron Weiner said:
rasta

It just occurred to me that it can be done in one with one loop by designing
a query that uses a sub query as it criteria. For example:

Select SpecifcCol as DupName
From SpecifcTable
Where SpecifcCol In
(Select SpecifcCol
From SpecifcTable
Group By SpecifcCol
Having Count(SpecifcCol)>1)
Order By WhateverMakesSense

Open this query in a recordset and iterate that recordset updating DupName
along the way. Here is some Pseudo Code that you should be able to convert
pretty easily into a working subroutine.

Open RS (using the Select statement above)
strOldName = ""
Do While not RS.EOF
if RS!DupName = strOldName then
strOldName = RS!DupName
RS!DupName = RS!DupName & i
RS.Update
i = i + 1
Else
strOldName = RS!DupName
i = 2
End If
RS.MoveNext
Loop
Close RS

Make a backup and test there before submitting your real data to this
potentially disastrous procedure. Obviously none of this has been tested.

Ron W
www.WorksRite.com
Click to expand...

Ron, I think this is the right approach but you're making it a bit more
complex than need be. Only a single pass through a recordset is needed
provided that the recordset is ordered by the field that is being updated.

Create a recordeset with "select field from table order by field"
str = rst.field
i = 1
rst.next
While not rst.eof
if rst.field = str then
rst.field = str & i
i = i + 1
rst.update
rst.next
else
str = rst.field
i = 1
rst.next
end if
wend
rst.close

And, of course, the most important part is the last statement above (re:
backup and test first)
 
R

rasta

thank you randy/ron,

i've create a module in the access mdb containg the tbl1 as show below.
a compile error is showing up 'method of data member not found'


Option Compare Database

Sub Main()

Dim Str As String
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cn = CurrentProject.AccessConnection

Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = cn
.Source = "select field1 from tbl1 order by field1"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

Str = rst.field1

i = 1
rst.Next
While Not rst.EOF
If rst.field1 = Str Then
rst.field1 = Str & i
i = i + 1
rst.Update
rst.Next
Else
Str = rst.field1
i = 1
rst.Next
End If
Wend
rst.Close

End Sub
 
R

Randy Harris

rasta said:
thank you randy/ron,

i've create a module in the access mdb containg the tbl1 as show below.
a compile error is showing up 'method of data member not found'


Option Compare Database

Sub Main()

Dim Str As String
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cn = CurrentProject.AccessConnection

Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = cn
.Source = "select field1 from tbl1 order by field1"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

Str = rst.field1

i = 1
rst.Next
While Not rst.EOF
If rst.field1 = Str Then
rst.field1 = Str & i
i = i + 1
rst.Update
rst.Next
Else
Str = rst.field1
i = 1
rst.Next
End If
Wend
rst.Close

End Sub
Click to expand...

What I had written was merely pseudo code (and rather sloppy at that). I've
cleaned it up. Here is a procedure that will actually do exactly what you
asked for:


Dim Str As String
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim I As Integer
Dim SQL As String

Set cn = CurrentProject.Connection
SQL = "select field1 from t1 order by field1"
rst.Open SQL, cn, adOpenKeyset, adLockOptimistic

Str = rst!field1
I = 1
rst.MoveNext
While Not rst.EOF
If rst!field1 = Str Then
rst!field1 = Str & I
I = I + 1
rst.Update
rst.MoveNext
Else
Str = rst!field1
I = 1
rst.MoveNext
End If
Wend
rst.Close

This works, I tested it. In the line that assigns that SQL statement above,
you'll need to substitute your actual table and field name for t1 and
field1.

HTH,
Randy
 
R

rasta

thank you randy - perfect


Randy Harris said:
What I had written was merely pseudo code (and rather sloppy at that).
I've
cleaned it up. Here is a procedure that will actually do exactly what you
asked for:


Dim Str As String
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim I As Integer
Dim SQL As String

Set cn = CurrentProject.Connection
SQL = "select field1 from t1 order by field1"
rst.Open SQL, cn, adOpenKeyset, adLockOptimistic

Str = rst!field1
I = 1
rst.MoveNext
While Not rst.EOF
If rst!field1 = Str Then
rst!field1 = Str & I
I = I + 1
rst.Update
rst.MoveNext
Else
Str = rst!field1
I = 1
rst.MoveNext
End If
Wend
rst.Close

This works, I tested it. In the line that assigns that SQL statement
above,
you'll need to substitute your actual table and field name for t1 and
field1.

HTH,
Randy
Click to expand...
 
Top