Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / March 2008

Tip: Looking for answers? Try searching our database.

Adding ID field with numbers in increments of 100

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Bob - 31 Mar 2008 17:31 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
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.