create alphanumeric code

P

pricter

I would like a alphanumeric code in my access table where the first section
would be consisted of the first letter of the surname and the second of the
increasing number of the surname with the same first letter surname. For
example If I have three name Smith, Montali, Sound the first code for Smith
would be S1, for Montali M1 and for Sound S2 any suggestion who do that???
 
J

John Vinson

I would like a alphanumeric code in my access table where the first section
would be consisted of the first letter of the surname and the second of the
increasing number of the surname with the same first letter surname. For
example If I have three name Smith, Montali, Sound the first code for Smith
would be S1, for Montali M1 and for Sound S2 any suggestion who do that???
Click to expand...

This is called an "Intelligent Key" - and that's not a compliment,
unfortunately.

What do you plan to do if you have 384 records with names beginning
with B, and you need to add a new record for "Baalam"? Will all the
alphanumeric codes need to be updated? Where do they need to be
updated: just in this table? In other linked tables? On Post-It notes
and in people's minds?

Perhaps you could explain why you need this field and what use you
intend to make of it. If you want to create this field dynamically,
you could use an expression such as

AlphaCode: Left([surname], 1) & DCount("*", "[yourtable]", "[Surname]
LIKE " & Left([Surname], 1) & "*")

but the number you get will change whenever the contents of the table
change.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
M

Mike Painter

pricter said:
I would like a alphanumeric code in my access table where the first
section would be consisted of the first letter of the surname and the
second of the increasing number of the surname with the same first
letter surname. For example If I have three name Smith, Montali,
Sound the first code for Smith would be S1, for Montali M1 and for
Sound S2 any suggestion who do that???
Click to expand...

John's covered it pretty well.
I'd point out that such systems were of value in the days of file cabinets
and the very early days of computers but are of no value today.

Use an autonumber as a key and a combobox to look up the person by the last
name.
It will work quite well on even a 300 MHz machine until you get past 20,000
records or so. (Then the combo box limitations set in and you would have to
modify the combo a bit.)
 
P

pricter

I would like to have such a number because It will be a unique number for
every person. Furthermore my problem is that this number I would not like to
change and If it could be created automatically. For example if I have three
names such Smith Bound and Sound with numbers S1 for the first B1 for the
second and S2 for the third for a new record such as Brilliant would get B2.
Furthermore if I delete a person with S the new record will get the S3 Is
this possible
 
J

John Vinson

I would like to have such a number because It will be a unique number for
every person. Furthermore my problem is that this number I would not like to
change and If it could be created automatically. For example if I have three
names such Smith Bound and Sound with numbers S1 for the first B1 for the
second and S2 for the third for a new record such as Brilliant would get B2.
Furthermore if I delete a person with S the new record will get the S3 Is
this possible
Click to expand...

It's possible, and I'll describe how below.

But it is still A VERY BAD IDEA.

Such sequential numbers maybe made sense, fifty years ago, when they
were needed to put onto file drawers or as the Only Searchable Field
in a flat-file database. They make next to no sense today.

After you get records in the database for Brilliant, Bound, Brassy,
Bossanova, Buzz, and Bodacious - and you have B1, B2, B3, B4, B5, and
B6 - how will you know at a glance, or even with some research, which
is which? What *good* does a B5 label on the record tell you (other
than if it were a soundtrack to Babylon 5)? They will not be in
sequential order alphabetically (you don't want them to change, so if
you add "Baa Baa Blacksheep" three months from now it won't be B1); if
they're chronological, you won't be able to remember "Did I enter
Bound before entering Brilliant, or after?" I cannot see that it does
you ANY good.

That said... you can have an Integer field which I'll call SEQ in your
table. Do all your data entry using a Form. In the AfterUpdate event
of the name field (which should not be called NAME, since that's a
reserved word - I'll assume you have a textbox named txtNamefield, and
a textbox txtSeq) put code like:

Private Sub txtNamefield_AfterUpdate()
If Not IsNull(Me!txtNamefield) Then
Me!txtSeq = NZ(DMax("[Seq]", "[yourtable]", "[namefield] LIKE " _
& Left(Me!txtNamefield, 1) & "(*")) + 1
End If
End Sub

When you want to see the "B4" or whatever, use a calculated field in a
Query or in the control source of a form or report textbox:

Left([namefield], 1) & [seq]


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top