How to add autoincrement field to a table with duplicated records?

[Amy]

I'm trying to add an autoincrementing id to a table based on an
existing field Name, but Name has duplicated records. How can I do
that in ACCESS?

Thanks.

Amy

[Larry Linson]

From the messagebox that appears when you try to change a data type with
data in it (duplicate or not) to Autonumber:

"Once you enter data in a table, you can't change the type of any field to
AutoNumber, even if you haven't yet added data to that field.

Add a new field to the table and define its data type as AutoNumber.
Microsoft Access then enters data in the AutoNumber field automatically,
numbering the records consecutively starting with 1."

To put predefined numbers in an AutoNumber field, you have to create a new
table with the AutoNumber field to correspond to a Long Integer field in the
original, and other fields of the same type as the original -- then append
the information from the original.

From your description, I am not quite certain just what you mean by "add an
autoincrementing id to a table based on an
existing field Name". One thing... "Name" isn't a good field name because it
is an Access reserved word and sooner or later it'll cause you trouble.
Second, I'd figure that to be a Text field not a Long, so you need to
clarify.

I'm not at all sure I understand why you want to do what you describe, if
there would be duplicate numbers in that field, because the purpose of an
AutoNumber is to provide a unique identification for records just for
internal use in your application... e.g., joining related tables.

However, a field defined as AutoNumber can have data appended that results
in duplicate numbers in the field, provided you haven't set it as a primary
key, or set its properties to disallow duplicates.

Larry Linson
Microsoft Access MVP
"Amy" <ww**@shaw.ca> wrote in message
news:6c**************************@posting.google.c om...

I'm trying to add an autoincrementing id to a table based on an
existing field Name, but Name has duplicated records. How can I do
that in ACCESS?

Thanks.

Amy

[amy]

Sorry I didn't make it clear. My table has a field called User_Name,
which contains duplicated names. To anonymize this field, I'd like to
assign each user a unique ID and use the ID as personal identifier
instead. Any good method to do this?

Amy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Salad]

amy wrote:

Sorry I didn't make it clear. My table has a field called User_Name,
which contains duplicated names. To anonymize this field, I'd like to
assign each user a unique ID and use the ID as personal identifier
instead. Any good method to do this?

Amy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Do realize autonumbers can have skips in them. They won't always be
sequential...ex...you start to add a record, decide you don't want to add
a record and undo the changes, the next time you add you will have a skip
in numbers. 1,2,3,5. If you can live with that then my suggestion
is....

Select tables tab. Select your current table. Click CTRL+C then
CTRL+V. This will prompt you for a new table name. Enter a new name and
select STRUCTURE ONLY. Now open up the new table name and add/create an
autonumber field. This is an excellent field to make as your primary key
(this way other tables can use this new field in it to link to it) as
there will be no duplicates. Save the new table.

Now goto queries new. Queries/New/Design. Select the original table.
Drag the * to the first column. From the menu, select Query/Append. The
wizard will ask which table you want to append to. Select the new
table. Now run the query. Close and save.

Open up the new table. If all of the data is there as you expect, close
it and then select the original table, right-click and select rename.
Add the word Original to the end of the name. Now do the same, but
rename the new file to the old, original file name.

Once you are assured everything is hunky-dory, you can delete the old,
original table.

[amy]

Hi Salad:

Thanks for your message. But that didn't solve the problem with
duplicate records. For example, If there are four Smith in the User_Name
field, I'd like the ID to be the same for these four records.

Amy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Roger]

are the four 'Smith's the same person ?
if not, you'll still need 4 records
if so, delete three of them once you created the auto key..
amy <an*******@devdex.com> wrote in message news:<40***********************@news.frii.net>...

Hi Salad:

Thanks for your message. But that didn't solve the problem with
duplicate records. For example, If there are four Smith in the User_Name
field, I'd like the ID to be the same for these four records.

Amy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Salad]

amy wrote:

Hi Salad:

Thanks for your message. But that didn't solve the problem with
duplicate records. For example, If there are four Smith in the User_Name
field, I'd like the ID to be the same for these four records.
Ahhhh....an important fact you left out. Well, here is what I might do. Do
a Ctrl+C, Ctrl+V and create a copy of the originial (structure and data)

Let's say that the originial table is called Table1. Let's say there are
thow fields; LastName and FirstName. We could create a query of that table
(the originial), sort it on Last name first name and call it Query1.
Please substitute the field/query names with your own. Now add a field to
the original called DUPE, type Text, Len 1

'i'm writing this w/o verifiying. Please compile the sub before running. I
am using DAO
Sub DeleteDuplicates()
Dim rst As DAO.Recordset
Dim strTest As String
set rst = Currentdb.openrecordset("Query1",dbopendynaset)
rst.MoveFirst
Do while not rst.EOF

'first record of a possible dupe is set to N
strTest = NZ(rst![LastName],"") & NZ(rst![FirstName])
rst.Edit
rst!Dupes = "N"
rst.Update

rst.MoveNext

'check if not end of file
If Not rst.EOF Then
'check if we have a duplicate. If the first/last name matches
'the first/last name of the first record of the set, flag as a
dupe
If strTest = NZ(rst![LastName],"") & NZ(rst![FirstName]) then
'yes, we have a duplicate
Do while Not rst.EOF And strTest = NZ(rst![LastName],"") &
NZ(rst![FirstName])
'set all dupes to Y
rst.Edit
rst!Dupes = "Y"
rst.Update

rst.MoveNext
Loop
Endif
Endif
Loop
rst.close
set rst = Nothing
msgbox "All dupes are flagged as Y"
End sub

You can create a new module, cut/copy this code to the module, compile, then
run. Remember to change the query name, lastname, first name to reflect
your table/field names.

Now open up the query (Query1). As you scan down the list you can see the
records the sub flagged as a dupe. If you are satisfied, create a query to
select all records in the table that has Dupe set to Y. Then from the menu
select Query and make it a Delete query. Run it. I suggest you make a
backup copy of the table prior to deleting.

Now you can add the autonumber if you want.




Amy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[amy]

Thank you all for your replies. The problem is solved.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!