Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatic Unique ID Assignment (Access2K)

    I have a user who is set on using first and last name combination as unique ID. I am having problem with customers who have the same first and last name. I thought of solving the problem by assigning a sequence number to each lastname and firstname so that each will become a unique identifier. For e.g. if John Smith was in the database first, his unique identifier will be JSM001. Then later if, Jack Smith is entered into the system his unique identifier will be JSM002 and so on. And then later if Alan Smith came into the system, his unique ID will be ASM001. How can I program Access to automagically look for the last occurence of similar unique ID and assigned the correct ID in the correct sequence. I hope I am making sense to someone. Here is a sample data and the desired unique ids. I know that using autonumber will work better but I am working with existing data that are already there. I only want to use the same logic using Access. The previous programmer created the original database using a very early version of FoxPro. Thank you in advance for your help.

    FName LName UniqueID
    John Smith JSM001
    Jack Smith JSM002
    Alan Alda AAL001
    Joe Smith JSM003
    Albert Alda AAL002

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Automatic Unique ID Assignment (Access2K)

    It involves the use of considerable code - and you might want to split the field into an alpha (XXM) and a number. If you are pretty comfortable with VBA, it involves finding any records that have the same ID you are proposing (using DAO or ADO), and if there are adding one to the numeric portion and repeating the process. But frankly it's a very inefficient thing to do unless you force the user to do it and deal with any duplicates. Autonumbers are much more efficient both in terms of memory used and in terms of speed of additions.
    Wendell

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic Unique ID Assignment (Access2K)

    Hi deekatz

    First you should take a course in salesmanship 101 and show the advantages and disadvantages of users desired approach.

    Possibly review sample databases with your user that you have done for others.

    What is the purpose of the unique ID?

    Is it to look up records?

    Is it to link records together?

    Why does the user want what he is asking for?

    With the features of Access I would never use anything by auto number to link master and detail records together.

    If customer is adamant for a unique value, you could use something like the following:

    Individuals, first 4 characters of last name and dob mmddyy

    BROW020368
    SMIT040644
    THOM121490

    Companies, first 4 characters of company name and phone number

    BUFF8563456
    MARI7542473
    STEL5671423

    FName LName DOB
    JohnSmith010389
    JackSmith062243
    AlanAlda070356
    JoeSmith030344
    AlbertAlda122378

    You could still run into duplicate values depending on size of file, may have to increase size from 4 to 5, 6 etc.

    If the unique ID is for lookup purposes, customer on the phone, Access allows you unlimited number of ways to design a user interface for your user to find any customer or individual record that he is looking for in seconds.

    HTH

    John

Posting Permissions

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