1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Sequential numbering of records with record number

Discussion in 'Business Applications' started by DigitalDoc4, Jul 20, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. DigitalDoc4

    DigitalDoc4 Thread Starter

    Joined:
    Jul 20, 2012
    Messages:
    2
    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.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  3. DigitalDoc4

    DigitalDoc4 Thread Starter

    Joined:
    Jul 20, 2012
    Messages:
    2
    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
As Seen On...

Welcome to Tech Support Guy!

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1061947