There's no such thing as a stupid question, but they're the easiest to answer.


Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Sequential numbering of records with record number


DigitalDoc4's Avatar
DigitalDoc4 DigitalDoc4 is offline
Computer Specs
Member with 2 posts.
Join Date: Jul 2012
Experience: Intermediate
20-Jul-2012, 09:59 PM #1
Solved: Sequential numbering of records with record number
I am currently running Access 2007 on an Acer Aspire computer running Windows 7. I also use a recent HP desktop running Windows XP Professional and Access 2007 at work. I am setting up a database that has 830,031 records & 100 + fields in the main data table. Presently, the table has no unique record ID that could be used as a primary key. I want the table to be sorted in a specific (chronological) order, which I can easily do with a simple query. I have added a new empty field called D_Rec_ID, and I want to fill this field with the record number for each record in the sorted order. Once the database is completed, the number of records will not change. How can I populate this field with sequential record numbers? The only thing I can get to work is to set up an Excel spreadsheet with blocks of 8,000 sequential numbers, copy each block of numbers, and paste it into the Access table in the appropriate location. I can only do 8,000 at a time without exceeding the MaxLocksPerFile on my work computer (set at 9,500), which I canít change. This is an extremely tedious process and prone to mistakes. Any suggestions would be greatly appreciated.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,825 posts.
Join Date: Mar 2005
Location: UK
21-Jul-2012, 05:19 AM #2
DigitalDoc4, welcome to the Forum.

I assume this "database" is only going to be used for Searching?

To do what you want Copy the Table and Paste - Structure Only.
Set your new field to Autonumber.
Now create the the Query and sort it in the order that you want.
Convert the query to an Append query and select the new table as the table to be appended to.
Save and Run the query.
The records should now be entered in the new table in the order that you want with new ID numbers in that order.
I do not give up easily
DigitalDoc4's Avatar
DigitalDoc4 DigitalDoc4 is offline
Computer Specs
Member with 2 posts.
Join Date: Jul 2012
Experience: Intermediate
21-Jul-2012, 11:56 AM #3
Sequential numbering in Sorted Access Table
It worked like a charm! Thank you so much OBP!

Yes, the database will be used for searching using crosstab, frequency, and other counts of historical data. It has numerous other related tables, primarily for decoding the coded data stored in the master table. Fortunately the database was under 1GB (leaving it under the 2GB limit for Access databases after the append). I can, however, see how I could have the "structure only" table in one database and run the append query on the "data table" in another database if I was starting out with a data table that was pushing 2GB. Thanks again.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools

You Are Using: Server ID
Trusted Website Back to the Top ↑