Hey all,
Hopefully someone here can help me, I've got a problem and not sure how
to aproach it. I'm using Access 2003 and have a table with one field:
FileID in it there are many values, many of which are duplicates. I
need a way for a Access to automatically add decimals to duplicate
values. For example: I have values: 1200
1200
1200
1600
1600
and I need them to be auto-decimaled as: 1200.1
1200.2
1200.3
1600.1
1600.2
How can I do this, does it involve a query or macro I can make?
This is a very unorthodox approach, but if you want to continue here it
goes: 1) Create a Select - Totals Query as such, this will give you the
number of dups for each value in the Main Table.
FieldID Duplicate Count (CountOfFieldID)
77 3
135 3
1200 6
1500 4
1600 3
3349 2
2) Run this code wherever appropriate:
Dim MyDB As Database, rstDups As Recordset, rstMain As Recordset
Dim NumberOfMatches As Integer
Set MyDB = CurrentDb()
Set rstDups = MyDB.OpenRecordset("qryDuplicates")
rstDups.MoveLast: rstDups.MoveFirst
Set rstMain = MyDB.OpenRecordset("Table1")
rstMain.MoveLast: rstMain.MoveFirst
'================================================= ========
Do Until rstDups.EOF
Do Until rstMain.EOF
If rstDups![FieldID] = rstMain![FieldID] Then
NumberOfMatches = NumberOfMatches + 1
If NumberOfMatches > 1 Then
rstMain.Edit
'Assuming not > 9 duplicates - must revise if not True
rstMain![FieldID] = rstMain![FieldID] + ((NumberOfMatches - 1) / 10)
rstMain.Update
End If
End If
rstMain.MoveNext
Loop
NumberOfMatches = 0
rstMain.MoveFirst
rstDups.MoveNext
Loop
'================================================= =======
rstDups.Close
rstMain.Close