Getting unique field data

  • Thread starter Thread starter Jim Mc
  • Start date Start date
J

Jim Mc

I want to get data from existing field to make and ID
field. I was thinking of somehow getting the 1st letter
of the FirstName field and possibly the 1st letter of the
LastName field, combining them with the date to form a
unique ID. Can you help me with this? Thanks.
 
If you are making a surrogate key, to be used for linking tables in
relationships, why not use an Autonumber, that is what they are designed
for.

You can of course use other things, but you *have* to ensure they are
unique, and that is the tricky part.

If only one user will ever be creating the records, you could use the
Date/time, recorded as a single number ( actually an 8 byte Double ), but it
has no real advantage over an Autonumber - a 4 byte integer.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
I want to get data from existing field to make and ID
field. I was thinking of somehow getting the 1st letter
of the FirstName field and possibly the 1st letter of the
LastName field, combining them with the date to form a
unique ID. Can you help me with this? Thanks.
Click to expand...

This is called an "Intelligent Key" - and that's not a compliment,
unfortunately! It was a common technique thirty years ago when
databases needed one meaningful unique field... but Access doesn't.

Such ID's are hard to create (and ensure uniqueness - suppose you had
Jim Smith and Jane Symonds on the same day; if you will EVER have two
entries on the same day you run this risk); hard to maintain (suppose
Jane Symonds marries and becomes Jane Witherspoon - do you change her
ID, or do you just leave the confusing "JS040517" as Jane
Witherspoon's ID?), and not in fact of any real use to the end user
(who will need to look up the data in the table to figure out who is
meant in any case).

Just use an Autonumber; conceal it from view; and use the data
searching tools to allow users to view and search by the data actually
stored in the database.
 
Back
Top