Adding ID field with numbers in increments of 100

[Bob]

I am new to Access, so please forgive my "beginner" question.
I have a 1-column (Client Name) table with ~292,000 records. The records
are already sorted in alphabetical order, and there are no duplicate records.
I want to add a second column (ID) to the table, but I need to have the ID
start at 100500 and increment in multiples of 100.

Can someone kindly tell me how to do this using a query, but without having
to write any code (if that's possible)? Any help would be greatly
appreciated.

Thanks,
Bob

 

[fredg]

I am new to Access, so please forgive my "beginner" question.
I have a 1-column (Client Name) table with ~292,000 records. The records
are already sorted in alphabetical order, and there are no duplicate records.
I want to add a second column (ID) to the table, but I need to have the ID
start at 100500 and increment in multiples of 100.

Can someone kindly tell me how to do this using a query, but without having
to write any code (if that's possible)? Any help would be greatly
appreciated.

Thanks,
Bob

Why the hesitation on using code. That's the way to go.

Back up your table data first.

Then add a new field to your table.
Field Name: [Id]
Datatype: Number
FieldSize: Long Integer.

Then copy and paste the below code into new Module:

Public Sub AddIDNumbers()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("YourTableName")
Dim lngX As Long
lngX = 100500
rs.MoveFirst
DoCmd.Hourglass True
Do While Not rs.EOF
With rs
.Edit
!Id = lngX
.Update
.MoveNext
End With
lngX = lngX + 100
Loop

DoCmd.Hourglass False
Set rs = Nothing

End Sub
************
Run the procedure and it will update your table.

Note: with 292000 records, that last ID value will be 29,300,400.
Are you absolutely sure this is what you want to do?
Why?

 

[John Spencer]

UPDATE [TheTable]
SET ID = 100500 + DCount("*","[TheTable]","[Client Name]<""" & [Client Name]
& """") * 100

That will be slow.

Alternative would be to add records to a new table that has the structure
you want using a query like the following. I think that this should be
considerably faster, especially if you have indexed the client name field in
the existing table.

INSERT INTO NewTable ([Client Name], ID)
SELECT A.[Client Name]
, Count (B.ClientName)*100 + 100500 as NewID
FROM [Existing Table] as A LEFT JOIN [Existing Table] as B
ON A.[Client Name] < B.[Client Name]
GROUP BY A.[Client Name]

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..