By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,479 Members | 1,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,479 IT Pros & Developers. It's quick & easy.

Unique Client ID

P: n/a
How can we create a unique and non-editable ID from client's first and
last name?
eg.: SmitJ1 for John Smith

Matuag

May 24 '07 #1
Share this Question
Share on Google+
28 Replies

P: n/a
Left([LastName],4) & Left([FirstName],1) & TryCount

Keep incrementing TryCount in a loop until you get a unique ID then create
the record with an append query. On your form, make the field read only.

Don't use it as a primary key, so when the last name changes you can change
the ID without changing your detail records.

"Matuag" <ma****@gmail.comwrote in message
news:11**********************@u36g2000prd.googlegr oups.com...
How can we create a unique and non-editable ID from client's first and
last name?
eg.: SmitJ1 for John Smith

Matuag

May 24 '07 #2

P: n/a
Per Matuag:
>How can we create a unique and non-editable ID from client's first and
last name?
eg.: SmitJ1 for John Smith
What function will it need to fulfill?
--
PeteCresswell
May 24 '07 #3

P: n/a
>
What function will it need to fulfill?
--
PeteCresswell
I just need it to identify the record. It already has a Primary Key
setup.

May 25 '07 #4

P: n/a
On May 25, 12:25 am, "paii, Ron" <p...@packairinc.comwrote:
Left([LastName],4) & Left([FirstName],1) & TryCount

Keep incrementing TryCount in a loop until you get a unique ID then create
the record with an append query. On your form, make the field read only.

Don't use it as a primary key, so when the last name changes you can change
the ID without changing your detail records.

Thanks Ron.
I could do this in a query. Now I do not want it to change even if
there is any change in Last Name. I want to use this to identify the
client's record.

May 25 '07 #5

P: n/a
"Matuag" <ma****@gmail.comwrote in message
news:11**********************@x18g2000prd.googlegr oups.com...

What function will it need to fulfill?
--
PeteCresswell

I just need it to identify the record. It already has a Primary Key
setup.
Define "to identify the record". The PK already does that.

Keith.

May 25 '07 #6

P: n/a
>
Define "to identify the record". The PK already does that.

Keith.
I want an alphanumeric ID rather than its auto numbered ID, for using
it on all manual records for clients. I am reluctant to use primary
key because I do not want users to know the total number of clients.

May 25 '07 #7

P: n/a
Per Matuag:
>I want an alphanumeric ID rather than its auto numbered ID, for using
it on all manual records for clients. I am reluctant to use primary
key because I do not want users to know the total number of clients.
The junk mail that I get in my mailbox seems to use a fraction of
the last name, the house number, and some fraction of the street
name a lot.

But it all still begs the question "Why". Is it so customers
can say over the phone something like "Cresswell 303 Friendship"
and the person at the other end can look up "Cress303Friend"?
--
PeteCresswell
May 25 '07 #8

P: n/a
rkc
(PeteCresswell) wrote:
Per Matuag:
>I want an alphanumeric ID rather than its auto numbered ID, for using
it on all manual records for clients. I am reluctant to use primary
key because I do not want users to know the total number of clients.

The junk mail that I get in my mailbox seems to use a fraction of
the last name, the house number, and some fraction of the street
name a lot.

But it all still begs the question "Why". Is it so customers
can say over the phone something like "Cresswell 303 Friendship"
and the person at the other end can look up "Cress303Friend"?
It sort of makes sense if it used on paper work (manual records?).

Cress303Friend would be entered to bring up the exact records for Pete
Cresswell instead of searching on something that brings up more than
one record.

Sort of.
May 25 '07 #9

P: n/a

"Matuag" <ma****@gmail.comwrote in message
news:11**********************@b40g2000prd.googlegr oups.com...
On May 25, 12:25 am, "paii, Ron" <p...@packairinc.comwrote:
Left([LastName],4) & Left([FirstName],1) & TryCount

Keep incrementing TryCount in a loop until you get a unique ID then
create
the record with an append query. On your form, make the field read only.

Don't use it as a primary key, so when the last name changes you can
change
the ID without changing your detail records.


Thanks Ron.
I could do this in a query. Now I do not want it to change even if
there is any change in Last Name. I want to use this to identify the
client's record.
You don't have to change it, but if you want is to provide an easily
remembered client ID then if the name changes, your users will want to
changing the ID. By providing a hidden ID for linking detail records,
changing the visible ID will not be a problem.
May 25 '07 #10

P: n/a

"Matuag" <ma****@gmail.comwrote in message
news:11********************@n15g2000prd.googlegrou ps.com...
>

Define "to identify the record". The PK already does that.

Keith.

I want an alphanumeric ID rather than its auto numbered ID, for using
it on all manual records for clients. I am reluctant to use primary
key because I do not want users to know the total number of clients.
It's a good ideal to hide the primary key, but changing the auto number
field to random would eliminate that as a source for counting number of
clients.
May 25 '07 #11

P: n/a
On May 25, 6:57 am, Matuag <mat...@gmail.comwrote:
Define "to identify the record". The PK already does that.
Keith.

I want an alphanumeric ID rather than its auto numbered ID, for using
it on all manual records for clients. I am reluctant to use primary
key because I do not want users to know the total number of clients.
Primary key has nothing to do witrh the nuimber of records nor does
autonumber.

May 25 '07 #12

P: n/a
On May 25, 12:15 pm, "paii, Ron" <p...@packairinc.comwrote:
"Matuag" <mat...@gmail.comwrote in message

news:11********************@n15g2000prd.googlegrou ps.com...
Define "to identify the record". The PK already does that.
Keith.
I want an alphanumeric ID rather than its auto numbered ID, for using
it on all manual records for clients. I am reluctant to use primary
key because I do not want users to know the total number of clients.

It's a good ideal to hide the primary key, but changing the auto number
field to random would eliminate that as a source for counting number of
clients.
Autonumber NEVER is a reliable determiniation of recrod count whether
you use random or not. That is NOT what autonumber is intended for.
Using Increment may give the APPEARANCE of the value equating to some
sort of record number but it does not actually do that. I always use
Random in my autonumber fields just so noone makes the false
assumptions that often follow an incremental autonumber.

May 25 '07 #13

P: n/a
Matuag <ma****@gmail.comwrote in
news:11**********************@u36g2000prd.googlegr oups.com:
How can we create a unique and non-editable ID from client's first
and last name?
eg.: SmitJ1 for John Smith
Don't. There is no need for such codes these days. Anyone who is
using them is stuck in 1970s thinking, back in the days when it
wasn't really possible to have onscreen lookups in your mainframe
computer application.

Now, to choose a client, you'd just have them search on name, and
then they'd select from the list of matches.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 25 '07 #14

P: n/a
Matuag <ma****@gmail.comwrote in
news:11********************@n15g2000prd.googlegrou ps.com:
I want an alphanumeric ID rather than its auto numbered ID, for
using it on all manual records for clients.
What's wrong with the client's name?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 25 '07 #15

P: n/a
rkc <rk*@rkcny.yabba.dabba.do.comwrote in
news:46***********************@roadrunner.com:
(PeteCresswell) wrote:
>Per Matuag:
>>I want an alphanumeric ID rather than its auto numbered ID, for
using it on all manual records for clients. I am reluctant to
use primary key because I do not want users to know the total
number of clients.

The junk mail that I get in my mailbox seems to use a fraction of
the last name, the house number, and some fraction of the street
name a lot.

But it all still begs the question "Why". Is it so customers
can say over the phone something like "Cresswell 303 Friendship"
and the person at the other end can look up "Cress303Friend"?

It sort of makes sense if it used on paper work (manual records?).

Cress303Friend would be entered to bring up the exact records for
Pete Cresswell instead of searching on something that brings up
more than one record.

Sort of.
You could also use the Autonumber PK if that's what you need.

And if you want to make it look cool and take up fewer characters,
run it through Hex().

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 25 '07 #16

P: n/a
Matuag <ma****@gmail.comwrote in
news:11********************@n15g2000prd.googlegrou ps.com:
I am reluctant to use primary
key because I do not want users to know the total number of
clients.
Then run it through Hex() -- then it will look like a semi-random
string of numbers and letters.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 25 '07 #17

P: n/a

"DavidB" <je***@yahoo.comwrote in message
news:11**********************@o5g2000hsb.googlegro ups.com...
On May 25, 12:15 pm, "paii, Ron" <p...@packairinc.comwrote:
"Matuag" <mat...@gmail.comwrote in message

news:11********************@n15g2000prd.googlegrou ps.com...
Define "to identify the record". The PK already does that.
Keith.
I want an alphanumeric ID rather than its auto numbered ID, for using
it on all manual records for clients. I am reluctant to use primary
key because I do not want users to know the total number of clients.
It's a good ideal to hide the primary key, but changing the auto number
field to random would eliminate that as a source for counting number of
clients.

Autonumber NEVER is a reliable determiniation of recrod count whether
you use random or not. That is NOT what autonumber is intended for.
Using Increment may give the APPEARANCE of the value equating to some
sort of record number but it does not actually do that. I always use
Random in my autonumber fields just so noone makes the false
assumptions that often follow an incremental autonumber.
My take on the original post was to hide even the APPEARANCE of a client
count.Using a random auto number will eliminate even the appearance of
meaningful information.
May 25 '07 #18

P: n/a
Per DavidB:
>Autonumber NEVER is a reliable determiniation of recrod count whether
you use random or not.
Which brings a thought to mind... If the OPs reason for not
wanting outsiders to see an AutoNumber value stems from not
wanting somebody to see that the endeavour doesn't cover that
many people, one could massage the table a little - by appending,
say 250,000 records via a query and the deleting them.... making
the first "real" record's AutoNumber PK be 250001... and thus
making the endeavour look larger that it is.
--
PeteCresswell
May 25 '07 #19

P: n/a
"paii, Ron" <pa**@packairinc.comwrote:
>My take on the original post was to hide even the APPEARANCE of a client
count.Using a random auto number will eliminate even the appearance of
meaningful information.
Which is a number about 10 digits long. Kinda cumbersome.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
May 27 '07 #20

P: n/a
On May 27, 11:42 am, "Tony Toews [MVP]" <tto...@telusplanet.net>
wrote:
"paii, Ron" <p...@packairinc.comwrote:
My take on the original post was to hide even the APPEARANCE of a client
count.Using a random auto number will eliminate even the appearance of
meaningful information.

Which is a number about 10 digits long. Kinda cumbersome.

Tony

I agree with Tony. Thats why I wanted a shorter ( 6 / 7 digit) number,
which is easy to quote, read and write.
May 27 '07 #21

P: n/a
Matuag <ma****@gmail.comwrote in
news:11*********************@n15g2000prd.googlegro ups.com:
On May 27, 11:42 am, "Tony Toews [MVP]" <tto...@telusplanet.net>
wrote:
>"paii, Ron" <p...@packairinc.comwrote:
>My take on the original post was to hide even the APPEARANCE of
a client count.Using a random auto number will eliminate even
the appearance of meaningful information.

Which is a number about 10 digits long. Kinda cumbersome.

I agree with Tony. Thats why I wanted a shorter ( 6 / 7 digit)
number, which is easy to quote, read and write.
Use a sequential Autonumber and display it with Hex(). Most people
won't be able to translate that into decimal.

For your users, you'll want a lookup that takes hexadecimal or
decimal numbers. If all the numbers are printed as Hex(), then
you'll want to default to hexadecimal.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 27 '07 #22

P: n/a
On May 27, 11:04 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
Matuag <mat...@gmail.comwrote innews:11*********************@n15g2000prd.googleg roups.com:
On May 27, 11:42 am, "Tony Toews [MVP]" <tto...@telusplanet.net>
wrote:
"paii, Ron" <p...@packairinc.comwrote:
My take on the original post was to hide even the APPEARANCE of
a client count.Using a random auto number will eliminate even
the appearance of meaningful information.
Which is a number about 10 digits long. Kinda cumbersome.
I agree with Tony. Thats why I wanted a shorter ( 6 / 7 digit)
number, which is easy to quote, read and write.

Use a sequential Autonumber and display it with Hex(). Most people
won't be able to translate that into decimal.

For your users, you'll want a lookup that takes hexadecimal or
decimal numbers. If all the numbers are printed as Hex(), then
you'll want to default to hexadecimal.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
OK. Hex seems to be a good idea. Thanks.

May 28 '07 #23

P: n/a
Matuag wrote:
On May 27, 11:04 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
Matuag <mat...@gmail.comwrote
innews:11*********************@n15g2000prd.googleg roups.com:
On May 27, 11:42 am, "Tony Toews [MVP]" <tto...@telusplanet.net>
wrote:
"paii, Ron" <p...@packairinc.comwrote:
My take on the original post was to hide even the APPEARANCE
of a client count.Using a random auto number will eliminate
even the appearance of meaningful information.
Which is a number about 10 digits long. Kinda cumbersome.
I agree with Tony. Thats why I wanted a shorter ( 6 / 7 digit)
number, which is easy to quote, read and write.
Use a sequential Autonumber and display it with Hex(). Most people
won't be able to translate that into decimal.

For your users, you'll want a lookup that takes hexadecimal or
decimal numbers. If all the numbers are printed as Hex(), then
you'll want to default to hexadecimal.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

OK. Hex seems to be a good idea. Thanks.
Actually, if you go with Hex() display I would also use random for the
AutoNumber. Otherwise the codes will be very similar looking within certain
data entry time-spans. With random you still get a nice 8 character
alpha-numeric value (with no worries about negative signs), but they will not be
in a close-set pattern. Someone is also more likely to see that they are an
incrementing value even when using Hex() if you stay with increment on the
AutoNumber.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
May 28 '07 #24

P: n/a
"Rick Brandt" <ri*********@hotmail.comwrote in
news:u9*****************@newssvr14.news.prodigy.ne t:
Matuag wrote:
>On May 27, 11:04 pm, "David W. Fenton"
<XXXuse...@dfenton.com.invalidwrote:
Matuag <mat...@gmail.comwrote
innews:11*********************@n15g2000prd.googleg roups.com:

On May 27, 11:42 am, "Tony Toews [MVP]"
<tto...@telusplanet.netwrote:
"paii, Ron" <p...@packairinc.comwrote:
My take on the original post was to hide even the
APPEARANCE of a client count.Using a random auto number
will eliminate even the appearance of meaningful
information.

Which is a number about 10 digits long. Kinda cumbersome.

I agree with Tony. Thats why I wanted a shorter ( 6 / 7
digit) number, which is easy to quote, read and write.

Use a sequential Autonumber and display it with Hex(). Most
people won't be able to translate that into decimal.

For your users, you'll want a lookup that takes hexadecimal or
decimal numbers. If all the numbers are printed as Hex(), then
you'll want to default to hexadecimal.

OK. Hex seems to be a good idea. Thanks.

Actually, if you go with Hex() display I would also use random for
the AutoNumber. Otherwise the codes will be very similar looking
within certain data entry time-spans. With random you still get a
nice 8 character alpha-numeric value (with no worries about
negative signs), but they will not be in a close-set pattern.
Someone is also more likely to see that they are an incrementing
value even when using Hex() if you stay with increment on the
AutoNumber.
Yes, but you'll only see that when you have more than one number in
front of you. And most people won't see it as suggesting any
particular number of clients, which was the main problem for the OP.

My only question on this is how to format hex numbers so they are
all the same length? Format(Hex(PersonID), "0000000") works only for
the values with all 0-9 digits and no letters. I guess you could use
padding instead.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 28 '07 #25

P: n/a

"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:7t********************************@4ax.com...
"paii, Ron" <pa**@packairinc.comwrote:
My take on the original post was to hide even the APPEARANCE of a client
count.Using a random auto number will eliminate even the appearance of
meaningful information.

Which is a number about 10 digits long. Kinda cumbersome.
Which is another reason to hide the PK from the user.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

May 29 '07 #26

P: n/a
David W. Fenton wrote:
My only question on this is how to format hex numbers so they are
all the same length? Format(Hex(PersonID), "0000000") works only for
the values with all 0-9 digits and no letters. I guess you could use
padding instead.
I must admit that I hadn't actually considered the length bit properly, but
was fooled by the statistics. Yes, you would have to use padded zeros to
ensure that all hex values would be 8 characters in length. However;
consider the following numbers...

I have an app used by nearly 300 remote users that submit records to our
corporate database that has been in use for over three years and is up to
48611 records so far. Of those NO hex value has less than 6 characters,
only 102 have 6 (0.2%), 1511 have 7 (3%), and all the rest have 8
characters. So any padding done would only affect (it would seem) a very
small percentage of all hex values.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
May 31 '07 #27

P: n/a
"Rick Brandt" <ri*********@hotmail.comwrote in
news:du****************@newssvr19.news.prodigy.net :
David W. Fenton wrote:
>My only question on this is how to format hex numbers so they are
all the same length? Format(Hex(PersonID), "0000000") works only
for the values with all 0-9 digits and no letters. I guess you
could use padding instead.

I must admit that I hadn't actually considered the length bit
properly, but was fooled by the statistics. Yes, you would have
to use padded zeros to ensure that all hex values would be 8
characters in length. However; consider the following numbers...

I have an app used by nearly 300 remote users that submit records
to our corporate database that has been in use for over three
years and is up to 48611 records so far. Of those NO hex value
has less than 6 characters, only 102 have 6 (0.2%), 1511 have 7
(3%), and all the rest have 8 characters. So any padding done
would only affect (it would seem) a very small percentage of all
hex values.
Yes, but if the app was converted to random Autonumber after the
table was already populated, you're going to have a lot of low
Autonumber values, and those are going to need to be padded. I
tested Hex() on just such data, and that's why I noticed it.

Do current versions of Access have a Pad() function or do you have
to write one for yourself? It's simple enough to do, of course, and
I"ve probably written one a half dozen different times, but have
never considered it useful enough to import into my code library.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 31 '07 #28

P: n/a
David W. Fenton wrote:
"Rick Brandt" <ri*********@hotmail.comwrote in
news:du****************@newssvr19.news.prodigy.net :
David W. Fenton wrote:
My only question on this is how to format hex numbers so they are
all the same length? Format(Hex(PersonID), "0000000") works only
for the values with all 0-9 digits and no letters. I guess you
could use padding instead.
I must admit that I hadn't actually considered the length bit
properly, but was fooled by the statistics. Yes, you would have
to use padded zeros to ensure that all hex values would be 8
characters in length. However; consider the following numbers...

I have an app used by nearly 300 remote users that submit records
to our corporate database that has been in use for over three
years and is up to 48611 records so far. Of those NO hex value
has less than 6 characters, only 102 have 6 (0.2%), 1511 have 7
(3%), and all the rest have 8 characters. So any padding done
would only affect (it would seem) a very small percentage of all
hex values.

Yes, but if the app was converted to random Autonumber after the
table was already populated, you're going to have a lot of low
Autonumber values, and those are going to need to be padded. I
tested Hex() on just such data, and that's why I noticed it.

Do current versions of Access have a Pad() function or do you have
to write one for yourself? It's simple enough to do, of course, and
I"ve probably written one a half dozen different times, but have
never considered it useful enough to import into my code library.
As far as I know there is none built in. I have always just coded it myself,
but as with you have never built a generic function for doing so.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
May 31 '07 #29

This discussion thread is closed

Replies have been disabled for this discussion.