Creating Unique IDs by concatenating info from fields

[GranthamIndoor]

It would be very useful for me to give each customer in my database a unique
customer ID based on several items including the first or first several
characters of different fields, perhaps brought together with a sequential
number at the end, including space holding zeros when necessary.
For example, Customer John Smith with a date of birth 5/1/1965 might be
SMIJ050165001. If another customer of the same name AND birthdate occured he
could be listed as JSMI050165002. My own driver's license number uses a
format vaguely similar to this so I assume it can be done.

Can I create a field in each customer record which stores this information
for later use? How would I do that if the field is drawing from information
which may not have been entered yet? Is there another (better?) way to do
this?

Thank you

 

[John W. Vinson]

It would be very useful for me to give each customer in my database a unique
customer ID based on several items including the first or first several
characters of different fields, perhaps brought together with a sequential
number at the end, including space holding zeros when necessary.
For example, Customer John Smith with a date of birth 5/1/1965 might be
SMIJ050165001. If another customer of the same name AND birthdate occured he
could be listed as JSMI050165002. My own driver's license number uses a
format vaguely similar to this so I assume it can be done.

Can I create a field in each customer record which stores this information
for later use? How would I do that if the field is drawing from information
which may not have been entered yet? Is there another (better?) way to do
this?

Stop.

This kind of "intelligent key" or "portmanteau key" is considered very bad
design. You WILL have duplicates and need to use a programmatically generated
number to distinguish them; it's hard to maintain; it messes you up if
somone's name changes; and it is absolutely useless for the user - is
SMIJ050165001 John Smith, or Janet Smithers? What do you do - look up the
person's birthdate and then find all the duplicates to determine?

I'd just use a *concealed* autonumber primary key as the "glue" to hold your
tables together. A properly designed form will let you use tools such as combo
boxes to easily select which customer you mean, by name and other
distinguishing features (address or phone number will be more accessible to
you and your staff).

Yes... you can do this. It's still a Bad Idea and not necessary.

 

[Fred]

A few thoughts:

Are you looking for this to be the primary key of the table? (simpler if
not).
How many records do you anticipate in your database? The answer might guide
your response to the next two questions.
- For the example that you describe, if a duplication (without that suffix)
is going to happen only once every 1000 years, you might not to spend the
time to handle that exception more elegantly.
- Or, even though usually not needed, you might just add an autonumber field
and use that as your suffix.
- Will this be a permanent ID? For example, if someone's name changes
(e.g. gets married) will their id # change?
Depending on the answer, you can:
- never load the id, but instead derive it on the spot when needed or
- load the id. For example, using code when you leave the record in a form.
Or a low tech codeless way would be to load it with a housekeeping update
query which is run periodically.

 

[Nicholas Meyer]

gj

 

[GranthamIndoor]

Fred-

Initially I had considered using this as a primary key as I would want each
of these ID's to be unique anyway. I'm ok with the notion of maintain the
traditional notion of a hidden, automatically generated, AutoNumber PK and
using this more in the user interface. My goal initally was to have the
entire ID generated automatically based on information entered into specific
fields AND to have some sort of query to determine a sequential increase in
the numbering field at the end of the ID (001, 002, etc) if necessary which
would provide me 999 unique cases even if I have multiple "JSMI" occurances
with the exact same birthdate. I also considered using the date of the ID's
creation (ie. "NOW()") as it's even less likely that 1000 customers with the
same name will be entered on the same day and it gives us a way to track when
they became customers. The likelihood of needing 1000 of those is pretty
slim with our current (or projected) customer base. I might even include
the first 2 characters of the first name to provide more info to the user and
more uniqueness. If this is an automatically generated ID number, and if I
keep a unique PK separate from this, it would be ok to change to a married
name and to create a new Customer ID for my purposes.

I think I would prefer to either A) have this ID generated immediately upon
saving a record (if necessary, at the click of a button labled "Generate
Customer ID" with a macro/VBA code which concatenates the fields), and saved
in the record, or B) generate the ID only for reports. The more upkeep and
maintenance is required, the less useful this database becomes.

Thanks.