Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ordering records in a table (2007)

    Is there a way to discern the order in which records are placed in a table? There is an autonumber field, but some records had been deleted and those numbers are being re-used now by newer records. Is there some other way now to figure out what the most recent records added were?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ordering records in a table (2007)

    Autonumbers are not reused unless you have reset the seed for them. Order of records is meaningless in a relational database. You would be ahead adding a timestamp to each record if you need to determine the order in which they were created, but what are you trying to accomplish with the information?
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ordering records in a table (2007)

    The ID in an Access table is usually an Autonumber which increments by 1 each time a new record is added. This is normally the Primary Key or "unique record identifier". Once a record is deleted the number that record had is never ever used again...hence unique.

    Methods you can use are to have a field with the default value Now() which inserts the date and time of the inserted record, but as it sounds like you have a recordset already you could use a SQL query like so:

    SELECT max([RecordID])
    FROM myTable

    Thi swill result in providing you with the highest RecordID, you will have to obviously change the fielfd name and the table name
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ordering records in a table (2007)

    Aw, is the problem of reused autonumbers cropping up in Access 2007? <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    You could add a field CreateDate to the table, of type Date/Time and set the Default Value property to Now().
    Whenever a new record is added, the CreateDate field in that record will be set to the current date and time.
    The CreateDate field should *not* be displayed on the form used to enter/edit records, or displayed in a locked control, so that users cannot modify its value.
    This will only help for new records created from now on; the field will be blank for existing records.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ordering records in a table (2007)

    Thanks to all of the above. As it turns out, I did reset the seeding a while back (I think it bothered me to have all of those unused numbers <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> ).
    Now, it turns out, that as new records are added (in this case, they are people registering to participate in an event), my wife suddenly decided that she needed to know who the last four to register were. Alas, I don't think I can easily tell her, since those "unused" numbers are scattered throughout the table. I was kind of hoping there was some secret identifier for each record that I was unaware of.
    I will henceforth and forevermore have the CreateDate in this table. Thanks to all!
    Warren

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ordering records in a table (2007)

    No, there is no secret identifier. And please don't ever seed the AutoNumber field to a number lower than the highest used number! (It's best to leave it alone)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •