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 - 31 Mar 2008 19:36 GMT
> 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
[quoted text clipped - 8 lines]
> 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?

Signature
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
John Spencer - 31 Mar 2008 19:48 GMT
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]

Signature
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.
>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
[quoted text clipped - 10 lines]
> Thanks,
> Bob