AutoNumber using concantinate

[James]

I want to create an Student ID that concantenates parts of
different fields in a table, for example I want
the "H"orner of the last name and the ***-**-"****" of a
social security number and the "**"/"**"/"****" of the
date to look as "H************". Is it possible to
concantinate fields together to create a field which would
be the visible AutoNumber? There will be a hidden
AutNumber as well just for recording purposes.

Thank you,

James R Horner

 

[Tim Ferguson]

I want
the "H"orner of the last name and the ***-**-"****" of a
social security number and the "**"/"**"/"****" of the
date to look as "H************".

Oh no you don't... the so-called "intelligent key" is actually a really Bad
Idea -- check out the definitions of 1NF and 2NF, both of which it manages
to break in one go.

Is it possible to
concantenate fields together to create a field

No: but it's easy to make it appear on the form or report. You can either
create it in the query (yes, it's a good idea always to base a form on a
query) by putting something like this in the top line of the query designer
grid (it wraps when in a posting like this, but you should enter it all on
one line)

FakeKey: Left$(LName, 1) & Right$(SSNumber, 4) &
Format(DateField,yyyymmdd")

Alternatively, you could put the same formula into the ControlSource
property of the textbox, but I think it's better to get the server to do
all the work like that.

You do realise don't you, that when Miss Horner gets married and changes
her name to Lamb, all your keys are going to be screwed don't you? And the
Hughes twins, who have the same initial and birthdate, have 1/1000 chance
of having the same right four digits to their SSN (and Murphy's law says
that one chance will come up within three months of signing off the
database)? This key is not guaranteed to be unique or stable: in other
words it's a bad candidate for a key in any sense.

There will be a hidden
AutoNumber as well just for recording purposes.

Well good: in which case what is the visible mock-up for? If you need to
sort on LName, BDate and SSN, then make a compound index on those fields.
Otherwise, I really can't see what it's good for.

HTH


Tim F

 

[James]

-----Original Message-----
"James" <[email protected]> wrote in

Thanks for the idea. Actually what I'm trying to do is
create a Student I.D. that would have unique information
based on the already given registration information.

 

[Tim Ferguson]

Actually what I'm trying to do is
create a Student I.D. that would have unique information
based on the already given registration information.

Why? You said you have an autonumber, so I cannot see the value in creating
and maintaining another kind of key. What do you think this extra field is
going to help you achieve that you can't without?


Tim F

 

[James Horner]

It is kind of hard. We are a private school but public
transportation gives our kids hard times because we don't
have student ID's or numbers. So I'm trying to create
automatic, non-sequential student numbers which will work
as the kids Student ID's.

 

[Tim Ferguson]

It is kind of hard. We are a private school but public
transportation gives our kids hard times because we don't
have student ID's or numbers. So I'm trying to create
automatic, non-sequential student numbers which will work
as the kids Student ID's.

I see I am referenced in the precursors of this message: but it must be a
long time ago as I have no idea of what it refers to. Can you remind us?


Tim F