Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2010
    Posts
    6

    Unanswered: Best way to give records an ID number?

    Hi guys, I have a simple split database with one form and one data table linked to the form, this database is for people that call in to protest their property value and a clerk enters in the persons info and makes a new record by filling out the form. My problem is assigning the records a log number or ID number, right now the ID number field set to auto number so when a new record is entered it gives it a new number but the numbers are getting out of sync as if someone deleted a record but no one has deleting anything. So whats the best way to give an ID number to exisiting records and new records being entered that wont come out of sync or make the user manually enter in a number.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There isn't one. If the number needs to match up with an external value you should have that number inputted.

    You wouldn't expect the computer to fill in firstname and forename - that would be silly. it is only because the numbers you have in the real world so very nearly match up with an automatically generated number that it is tempting to use one, however autonumbers should not be used if there is a real life counterpart they should match.

  3. #3
    Join Date
    Jan 2010
    Posts
    6
    so does that mean that no one ever keeps track of thier records in a
    database then? What i'm looking for is if there are 50 records in my data table I want an ID column that goes from 1-50 counting up with with the records going 1-50, we just want to be able to identify records using a number, instead of saying I need bobs record or marys record we want to say hey I need #335 or#336, the id number also gets printed out with the form, its how we identify our records,

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No. What I am saying is that if you have a number in the real world and you need that number in your database then you must enter it in to your database, not expect the database to guess it.

    Autonumbers are (almost exclusively) used to generate surrogate keys. These are numbers that are used to "abstract" the natural keys of the entities. The principle purposes of surrogate keys are:
    * Make the database, table and index footprints smaller so more information can be represented in less disk and RAM
    * Changes to natural keys do not need to be cascaded throughout the tables of the database.

    There are other features too but they are the main two. A surrogate key is a sort of pointer used by the database. It should not map directly to any number in the real world. It should not be exposed to users. It is for internal use only. It is for this reason that if you care one jot about gaps in an autonumber range of numbers, or things getting out of "sync", then you should not be using autonumbers.

  5. #5
    Join Date
    Jan 2010
    Posts
    6
    Ok thanks for clearing that up. The users were manually entering the ID numbers before but for the past two weeks we have been getting massive phone calls for protesting property values and we had a problem manually entering the id number since then, the problem is with multiple users entering new records and manually entering a ID number for those records different users would often enter the same ID number for a new record because when a user opens a form and doesn't finish entering a record before a different user opens a form, both users think the last record was ID#5 so they both enter the new record as ID#6
    and would cause a problem because it dosen't allow duplicate ID numbers, they would have to keep entering ID#7 ID#8 ID#9 untill it allowed them to save it.

    So thats why I switched to auto number so it just assigns the record the next available number but now thats not working, so whats the best solution, I tried to explain it the best I can.

  6. #6
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by brian56 View Post
    So thats why I switched to auto number so it just assigns the record the next available number but now thats not working, so whats the best solution, I tried to explain it the best I can.
    It really is working correctly, it is just not what you are expecting.

    There really is not way to a records numberd in order without an gaps int he sequence unless you renumber the records regularly. The problem with this is that the number can change for a record. So it can not be used to uniquely identify the record.

    There are several reasons you are getting gaps in the autonumber. The obvious is deleting.

    This also happens when someone starts a new record then does an "Undo" in some way so the new record is canceled without saving it. This will use a autonumber value.

    Autonumbers are not supposed to have no gaps is the sequence, but be a unique number that will never change so that you can identify the record. Which is what you need, even if there are gaps. IMHO, you will need to get used to having gaps in the numbers.

    The best way i know to eliminate gaps in the autonumber is to use an unbound form for adding records. Never allow records to actually be deleted, only flagged as deleted.

    hope this helps ...
    Last edited by HiTechCoach; 05-06-10 at 14:12.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Had the same problem want to match a number with the real world and have the computer create the same number.

    all I was add a Eventnumber when The user save the record it would get the Max of the Eventnumber + 1 to it then Tell the user This is Event Nº 100 please update the page work
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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