auto assigned number

[Winnie]

Can anyone tell me how to get the auto assigned number in
a new column as the unique PID or UID from a
concatenation of some other columns like LastName,
FirstName, DOB?

Thanks in advance.

 

[John Vinson]

Can anyone tell me how to get the auto assigned number in
a new column as the unique PID or UID from a
concatenation of some other columns like LastName,
FirstName, DOB?

Well... DON'T.

This kind of "intelligent key" is very difficult to make and ensure
that it is unique. If you're assuming that there are no two people in
the world (or even in the world that your database will have to deal
with) who happen to share the same name and birthdate, you're
mistaken.

If you want a meaningless, unique number just use a Custom Counter -
many posts on this newsgroup discuss how to create one. Names and
dates are DATA, not unique ID's.

 

[Winnie]

Hi John,

Thanks for your comments. Do you know how to make this
kind of serial number based on the unique records (from
concatenation of LName, FName, DOB)? Even there may be
some duplications in certain fields, the chance is
statistically very small and can be ignored in my case.
I'm doing a calculation eyeing on about the 98% of all
records with this so called "unique" ID. Pls help if you
can.

-----Original Message-----

 

[John Vinson]

Hi John,

Thanks for your comments. Do you know how to make this
kind of serial number based on the unique records (from
concatenation of LName, FName, DOB)? Even there may be
some duplications in certain fields, the chance is
statistically very small and can be ignored in my case.
I'm doing a calculation eyeing on about the 98% of all
records with this so called "unique" ID. Pls help if you
can.

It can be done.

IT SHOULD NOT BE DONE.

A 98% - or 99.95% - unique ID *is not a unique ID*. It's either unique
or it has potential duplicates! Why go to the effort to create a field
that you know has a likelihood of failure, when you have the choice to
create a unique numeric ID that will allow a guaranteed four billion
odd records before it duplicates?

If you want a unique Index on these three fields, create a unique
Index on the three fields. There is no need to jam them into one field
to do so. If you want to group by these three fields to do
calculations, group by these three fields to do the calculations.

That said... you can construct a nasty, redundant, useless, and
imperfect Intelligent Key by using an Update query, updating the field
to

[LName] & [FName] & Format([DOB], "mmddyyyy")

to create VinsonJohn051619456 as an ID.