Automatic Unique ID Assignment (Access2K)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote