none
Create unique ID combining letters and numbers

    Question

  • Hi,

    Help! I have to show a group of school kids how to do this but I haven't used Access in years so don't know how to do it myself :s

    For an Access project I need to be able to generate a unique student ID, which combines letters from the students name with a sequential number that is always one more than the highest currently in use. I have a forename field so I plan on using the first two letters of the forename plus a unique number. Is it possible to auto ID this?

    Any advice would be appreciated.
    Paul
    Sunday, December 30, 2012 1:31 PM

Answers

  • It's a bad idea to store a calculated field in the database; it's a violation of the rules of normalization. Your table can have
    StudentID autonumber PK
    FirstName text(20) required
    'etc.

    Then do this in a query:

    select Left$(FirstName, 2) & StudentID from Students


    -Tom. Microsoft Access MVP

    Sunday, December 30, 2012 4:41 PM

All replies

  • (Assuming Access 2013)

    Simplest:

    Create a table with an AutoIncrement number ([ID]), a field1 as short text, and a third field [Field2] as a Calculated Field that is as:

    Str$([ID])+Field1

    Then place a few records (student names) into Field1 and

    Select Field2 from Table1

    (From Create Menu - use Query Design)

    (From Design View, you can pull the View and Run menu)


    R, J

    Sunday, December 30, 2012 4:05 PM
  • It's a bad idea to store a calculated field in the database; it's a violation of the rules of normalization. Your table can have
    StudentID autonumber PK
    FirstName text(20) required
    'etc.

    Then do this in a query:

    select Left$(FirstName, 2) & StudentID from Students


    -Tom. Microsoft Access MVP

    Sunday, December 30, 2012 4:41 PM
  • I considered the @@Identity but it seemed it might be too difficult a concept to pass on to students.  He did not say what age group the class was for.

    R, J


    • Edited by Crakdkorn Sunday, December 30, 2012 5:54 PM
    Sunday, December 30, 2012 5:53 PM