Unique ID

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hI,

I am a basic Access user but I want to find a way of changing a unique id.
Ie in my database I have memberid, at the moment this is autonumber but i
wish to change this so I can have the 1st letter of a member's firstname and
the 1st letter of a member's surname along with a number like 001. E.g a
member id could be AB001.
How do i do this?
 
I would recommend against this. Creating intelligent keys in general is a
bad idea since you generally have to create parsing routines to deal with
the individual part. If you need intelligent keys for query purposes then
you could create a column that will do as you ask or create a view but leave
the autonumber field as is.
 
To add to this comment from solex

I think what solex is recommending is keeping the autonumber and creating an
artificial field as required using the autonumber and name fields.
For example, if you have the following:

Autonumber_field, FirstName, LastName
1 Joe Brown
2 Sally Jones
: : :

then the following SQL (not checked, just typed straight in) would create an
artifical field that has something like what you want (I have not added the
extra 0's to make 1 = 0001, you can post back if you want info on that)

SELECT myTable.Autonumber_field, myTable.FirstName, myTable.LastName,
Left([FirstName], 1) & Left([LastName], 1) & [Autonumber_field] AS
MyNewField
FROM tbLabtest;

Autonumber_field, FirstName, LastName MyNewField
1 Joe Brown JB1
2 Sally Jones SJ2
: : :

So the original autonumber field is still there and doing all its regular
good work of preventing duplicates and automatically creating a new unique
number, we are just using it to generate a new field which is more to your
liking.

You can use this SQL created artificial field only when you want it (ie open
up your form/tables using a query which includes the SQL to create this new
field) which means that you never actually explicitly store the value (for
example the select query shown above makes MyNewField for you to view but it
does not store MyNewField anywhere), or you could be *very very* naughty and
redundantly add it into its own field when you add new records BUT
understand that it is not dynamic, meaning if Joe has a sex change and
changes his name to Abby, the artifically constructed field will not get
updated, it will stay as JB1 until you fix that field. The later is
something you might consider doing if you are not overly comfortable with
creating queries or views etc or if you have lots and lots of forms/queries
etc that currently uses the table and you think it will be too difficult to
update them. But bad things tend to happen to naughty people... meaning
there are always very good reasons for doing things the right way even if
they do not seem obvious now.

Furthermore solex is right about a continual need for parsing etc, if you
dont store this new field explicitly AND try to use it as a "key" in other
tables then you will have to constantly generate this new key on the fly for
every join etc What a pain.

Of course it ultimately depends on why you are wanting to make this change
as to what the best thing to do is. What is wrong with an autonumber being
the key? Perhaps the newsgroup community can offer suggestions/pros/cons if
you describe what it is about the current system that you dont like.

regards
A
 
Back
Top