473,320 Members | 1,883 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Adding decimals to duplicate values

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?

May 31 '06 #1
3 2160
On 31 May 2006 07:39:35 -0700, mu******@yahoo.com wrote:

You're probably doing something very eh unorthodox. Why don't you
share with us the bigger picture so we can give better advise.
It's very unusual to have a table with a single field, to then have
duplicates in that field, and to then want to change the data type
from (long) integer to single/double.
There's got to be a better way.

-Tom.

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?


May 31 '06 #2
ADezii
8,834 Expert 8TB
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
May 31 '06 #3

i guess that table doesnt have any relationship
with other table. Why not changing the data type
of fieldID to text.and if it aready in text data type
you can use this vba

Sub UpdateFieldID()
Dim rs As New ADODB.Recordset
Dim test As String
Dim ctr As Integer

rs.Open "Select * From Table4 Order by FieldID",
CurrentProject.Connection, adOpenStatic, adLockOptimistic
test = rs!FieldID
Do While Not rs.EOF
If rs!FieldID = test Then
ctr = ctr + 1
Else
ctr = 1
test = rs!FieldID
End If
rs!FieldID = rs!FieldID & "." & CStr(ctr)
rs.Update
rs.MoveNext
Loop
End Sub

Jun 1 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: joel | last post by:
I have a table which I want to update by dividing one field into another. The update runs with no errors, but the results come out as only a positive integer number. The datatype for the result...
1
by: Steve Leferve | last post by:
Hey folks I'm working on a 'duplicate' function. I need to duplicate related child records in the same subtable. Two questions: 1. My 'db.execute' function is not working. How can I trouble...
20
by: Ash Phillips | last post by:
Hi Everyone, I have this program I wrote in VB6 for family use. It's a DVD Database just for me to keep track of them cause I have so many lol. In VB6, I could add items to the ListView in...
6
by: Rudy | last post by:
Hi all, I know this is easy, just can't seem to get it. I have a windows form, and a text box, with a value already in it. I need to add that value to a table. It's just one value, so the entire...
6
by: orandov | last post by:
I am relatively new to SQL server. I am tring to send some decimal values to the database using a stored procedure with parameters of type DECIMAL. Every time it inserts the values into the...
4
by: FangQ | last post by:
hi I am very new to mysql. I have a question about using the "on duplicate update" clause with insert command. my table "data" has two columns, field1 and field2, where field1 is the index...
3
by: Jx4 | last post by:
Hi there, I am currently having trouble with some issues with adding Currency values. I have one column with values of transactions and the other column has a running total. It works great...
3
by: rajeshkrsingh | last post by:
Hi friends, Step1- create table duplicate ( intId int, varName varchar(50) ) insert into duplicate(intId,varName) values(1,'rajesh') insert into duplicate(intId,varName) values(2,'raj12')...
3
by: Celal | last post by:
Hi, I couldn't get around a problem about decimal rounding up when adding a record to a table from a form by programming a button. I have an invoice form where the grand total is a calculated text...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.