How can we create a unique and non-editable ID from client's first and
last name?
eg.: SmitJ1 for John Smith
Matuag
28  4072 
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
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
>
What function will it need to fulfill?
--
PeteCresswell
I just need it to identify the record. It already has a Primary Key
setup.
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.
"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.
>
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.
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
(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.
"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.
"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.
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.
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.
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/
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/
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/
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/
"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.
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
"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/
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.
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/
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.
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
"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/
"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/
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
"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/
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Tony Clarke |
last post by:
Hi,
I'm trying to write a system thats used for about 50 clients that uses html
forms and php to log details. The problem is that when a client loads the
form page it's given a value which is...
|
by: Louis Frolio |
last post by:
Greetings All, I have read many upon many articles here regarding GUID
data types and uniqueness. There have been many opinions regarding
the effectiveness of GUID's and when they should/should...
|
by: UJ |
last post by:
Is there any number I can get that is truly unique for a computer that can't
be changed? I want to have a system whereby I have a computer that accesses
a web service based on some unique value...
|
by: bwmiller16 |
last post by:
Guys -
I'm doing a database consistency check for a client and I find that
they're building unique indexes for performance/query reasons where
they could be using non-unique indexes.
Note...
|
by: Mamuninfo |
last post by:
Hello,
Have any function in the DB2 database that can generate unique id
for each string like oracle, mysql,sybase,sqlserver database.
In mysql:-
select md5(concat_ws("Row name")) from...
|
by: Goh |
last post by:
Hi,
I would like to know how can we implement a web page that
intelligent enough to unique identify that pc have been visit before without
any cookies and login user require.
I have try...
|
by: Jimmy Stewart |
last post by:
Ok, I'm trying to write a query that is starting to wear me down.
What I'm trying to do is create a year end report that gets sent to all of
my customers who meet two criteria. One they are...
|
by: tony obrien |
last post by:
Hi,
I have written a Class Library in VB.net which provides Props/Meths to a
caller to hide the uglinesses of a TCP message protocol between a Client
and a Server.
All this works just fine.
...
|
by: Mufasa |
last post by:
I'm looking for a way to get a truly unique identifier for a machine for our
client software. I'd like to have it so that there's little or no setup by
the end user. (We set up the machines and...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
