Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2003
    Location
    Staffs, England
    Posts
    18

    Unanswered: Unique record number

    Is there a variable or something that contains the record number for each record in a table?

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Maybe

    if the table incorporates a field marked as primary key, this should be the unique identifier.

    Furthermore, if this field is of type Autonumber, it will be a number

  3. #3
    Join Date
    Feb 2003
    Location
    Staffs, England
    Posts
    18

    Re: Maybe

    Yes, I know. the trouble with an autonumber is that it doesn't automatically put numbers in if you import data into a table.

    I have a primary key field and an auto number field, the primary key has to have letters in the field, because it is a part code. But I need the database to produce a unique number for each record.

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Primary key

    is your primary key not unique then?

    What about generating the unique id from a query as [Autonumber] & [PrimaryKey] or similer?

  5. #5
    Join Date
    Feb 2003
    Location
    Staffs, England
    Posts
    18
    The primary key is unique, the trouble is that it contains letters and I need a unique number for each record.

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Importing

    Is the importing something you do on a regular basis?

    if not try Dev Ashis's The Access Web...

    http://www.mvps.org/access/tables/tbl0005.htm

    For a possible solution

  7. #7
    Join Date
    Feb 2003
    Location
    Staffs, England
    Posts
    18

    Re: Importing

    I see how that would work, we were thinking of creating the numbers withing the excel spreadsheet and importing them.

    I take it that the database does not store a unique identifier for each record in a table automatically then?

  8. #8
    Join Date
    Jan 2003
    Location
    Aberdeen, Scotland, UK
    Posts
    168
    If you put in the data and then add an autonumber it wil be continuous all down the table.

  9. #9
    Join Date
    Jan 2003
    Posts
    46
    Deadcat,

    Unless I have overlooked it, you haven't indicated why you need a numeric value for the record identifier/primary key.

    The purpose of a primary key is to uniquely identify a record. Having a primary key with letters is perfectly acceptable.

    Besides uniqueness, primary keys should have another characteristic - they should be meaningless outside their function as a unique identifier. In other words, primary keys should not be data or data derived. The reason is that data inevitably changes. The primary key should never change. It sounds as if you are trying to use a primary key in some sort of data context and you should look at another avenue of handling this.

    A last note on autonumber fields. A number is automatically generated in an autonumber field when records are imported into a table. That is the AUTO part of autonumber. However your import is not going to work if you attempt to import data into an autonumber field.

    Come back with some more info as to what you are trying to flog Access into doing.

    Cheers,
    zambezibill

  10. #10
    Join Date
    Feb 2003
    Location
    Staffs, England
    Posts
    18
    I am using access to generate HTML cdoe for a form on a webpage, the form needs unique identifyers for each member within the form. These Identifiers, however, need to be numeric.


    A last note on autonumber fields. A number is automatically generated in an autonumber field when records are imported into a table. That is the AUTO part of autonumber. However your import is not going to work if you attempt to import data into an autonumber field.
    When we import data from an excel spreadsheet, leaving the autonumber field blank then Access also leaves the auto number blank and does not fill it in. How do you get it to automatically fill it in?

    Cheers,
    Dion

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    start of without autonumbers in mdb and xls
    import xls to mdb
    add an autonumber field to the access table
    ...the autonumber fills itself in.

    izy

  12. #12
    Join Date
    Feb 2003
    Location
    Staffs, England
    Posts
    18
    Originally posted by izyrider
    start of without autonumbers in mdb and xls
    import xls to mdb
    add an autonumber field to the access table
    ...the autonumber fills itself in.

    izy
    Can't do that. Records are already in the database and we need to import new stuff.

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740


    do you have to retain the SAME "autonumber" for the records already in the table... i guess yes, or you would have already realised that you could delete the autonumber field from the existing table, append the new records, and then insert a new autonumber field.

    so, a possible new strategy:

    import/link the xls to tblTemp

    open recordsets for the existing table and tblTemp
    step through the tblTemp recordset adding the new records to the existing table
    autonumber will do it's thing correctly

    ........stupid thought! have you tried something primitive like copy/paste-append.

    izy

  14. #14
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    If you use and append query to add the data the excisting autonumber should spawn new numbers for every appeneded record as long as you don't try to append anything to the auto munber field
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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