Autonumber using alpanumerics

I have created a client database for individual clients, which is working
well. My next assignment is to create a corporate database on Access 2000,
which understandably will be more complex in nature. There are two elements
of the database that I would like advice on; I hope I'm not asking for too
much in this thread. Basically I need a unique reference for each client.
Initially I had planned to use an autonumber, but I see the drawbacks of this
system in my initial database. I am wondering if it is still possible to have
some type of autonumber format as a ClientRef, using alphanumerics. The
format I want the ClientRef to follow is
000AAA, whereby the numeric figures are random numbers and the alpha figures
are relative to the client name, or if the client has many branches within
the group, then the alpha figures would represent the client name and the
branch name/location. I want this facility to be flexible enough that I am
able to manually create the alpha element of the reference . I am asking this
because (although there have been many references to the irrelevance of an
autonumber format if the client is to see/refer/have access to it) I want to
use it as a reference where the alpha section of the ClientRef will form part
of the InvoiceRef, therefore appearing in this format: AAA/00/00/0 whereby
the alpha figures are the client name/location, followed by the month, then
the year, and finally the invoice number for the month in question. If this
format were not possible by using the autonumber function, then I would
welcome any long-term feasible suggestions.

Thanks in advance
Katharine

Katharine

While the Access Autonumber data type is not, as you've learned, suitable
for what you are trying to do, there would be no reason you couldn't create
your own automatic numbering procedure (search for "custom autonumber" -- a
misnomer, but a strong search term).

By the way, your design may benefit from a bit more normalization. Your
"000AAA"-patterned "ID" includes more than one fact in the field, not a good
idea. Instead, use two fields in the table, and use a query to connect
(concatenate) them together for display.

Regards

Jeff Boyce
Access MVP

"Katharine Jansen" wrote in
message ...
I have created a client database for individual clients, which is working
well. My next assignment is to create a corporate database on Access 2000,
which understandably will be more complex in nature. There are two
elements
of the database that I would like advice on; I hope I'm not asking for too
much in this thread. Basically I need a unique reference for each client.
Initially I had planned to use an autonumber, but I see the drawbacks of
this
system in my initial database. I am wondering if it is still possible to
have
some type of autonumber format as a ClientRef, using alphanumerics. The
format I want the ClientRef to follow is
000AAA, whereby the numeric figures are random numbers and the alpha
figures
are relative to the client name, or if the client has many branches within
the group, then the alpha figures would represent the client name and the
branch name/location. I want this facility to be flexible enough that I am
able to manually create the alpha element of the reference . I am asking
this
because (although there have been many references to the irrelevance of an
autonumber format if the client is to see/refer/have access to it) I want
to
use it as a reference where the alpha section of the ClientRef will form
part
of the InvoiceRef, therefore appearing in this format: AAA/00/00/0 whereby
the alpha figures are the client name/location, followed by the month,
then
the year, and finally the invoice number for the month in question. If
this
format were not possible by using the autonumber function, then I would
welcome any long-term feasible suggestions.

Thanks in advance
Katharine

Hi Jeff

Thanks for the advice. I had just created another field in order to fulfil
part of the dilemma prior to reading your post. Your suggestion to
concatenate the two using a query should help to solve the remainder of my
problem.

Thanks for the prompt response.

Katharine

"Jeff Boyce" wrote:

Katharine

While the Access Autonumber data type is not, as you've learned, suitable
for what you are trying to do, there would be no reason you couldn't create
your own automatic numbering procedure (search for "custom autonumber" -- a
misnomer, but a strong search term).

By the way, your design may benefit from a bit more normalization. Your
"000AAA"-patterned "ID" includes more than one fact in the field, not a good
idea. Instead, use two fields in the table, and use a query to connect
(concatenate) them together for display.

Regards

Jeff Boyce
Access MVP

"Katharine Jansen" wrote in
message ...
I have created a client database for individual clients, which is working
well. My next assignment is to create a corporate database on Access 2000,
which understandably will be more complex in nature. There are two
elements
of the database that I would like advice on; I hope I'm not asking for too
much in this thread. Basically I need a unique reference for each client.
Initially I had planned to use an autonumber, but I see the drawbacks of
this
system in my initial database. I am wondering if it is still possible to
have
some type of autonumber format as a ClientRef, using alphanumerics. The
format I want the ClientRef to follow is
000AAA, whereby the numeric figures are random numbers and the alpha
figures
are relative to the client name, or if the client has many branches within
the group, then the alpha figures would represent the client name and the
branch name/location. I want this facility to be flexible enough that I am
able to manually create the alpha element of the reference . I am asking
this
because (although there have been many references to the irrelevance of an
autonumber format if the client is to see/refer/have access to it) I want
to
use it as a reference where the alpha section of the ClientRef will form
part
of the InvoiceRef, therefore appearing in this format: AAA/00/00/0 whereby
the alpha figures are the client name/location, followed by the month,
then
the year, and finally the invoice number for the month in question. If
this
format were not possible by using the autonumber function, then I would
welcome any long-term feasible suggestions.

Thanks in advance
Katharine

Jeff Boyce wrote:
Your "000AAA"-patterned "ID" includes more than
one fact in the field, not a good idea.

A single ISBN contains many facts and is called an 'intelligent key'.
Why do you think this is not a good idea?

a bit more normalization

Please be more specific e.g. which normal form are you recommending?

Jamie.

--

Jamie

Periodically the subject of "intelligent keys" shows up here in the
tablesdbdesign newsgroup. The consensus has seemed to me to argue against
combining multiple facts into a single field, since this represents a
violation of First Normal Form design. The underlying notion is "one fact,
one field".

That an ISBN "contains many facts" doesn't, in itself, qualify it as a good
example of a key, does it? In fact, I don't know if an ISBN is a single
field (then, by 1NF standards, it would not be a good design), or is itself
a concatenation of multiple, single-fact fields...

My comment on normalization was a "may help" not a recommendation, as I did
not have a full understanding of the current design.

Regards

Jeff Boyce
Access MVP

"Jamie Collins" wrote in message
oups.com...

Jeff Boyce wrote:
Your "000AAA"-patterned "ID" includes more than
one fact in the field, not a good idea.

A single ISBN contains many facts and is called an 'intelligent key'.
Why do you think this is not a good idea?

a bit more normalization

Please be more specific e.g. which normal form are you recommending?

Jamie.

--

Jeff Boyce wrote:
Periodically the subject of "intelligent keys" shows up here in the
tablesdbdesign newsgroup. The consensus has seemed to me to argue against
combining multiple facts into a single field

Is this the same consensus that recommends autonumber primary keys, by
any chance g?

The underlying notion is "one fact,
one field".

I've not seen this soundbite before. My google search:

http://groups.google.com/groups?q=%2....*&qt_s=Search

turned up nothing. Could you clarify your point, please?

That an ISBN "contains many facts" doesn't, in itself, qualify it as a good
example of a key, does it? In fact, I don't know if an ISBN is a single
field (then, by 1NF standards, it would not be a good design), or is itself
a concatenation of multiple, single-fact fields...

I'm not sure what you are getting at here. I know that the ISBN is an
excellent example of a key and that it contains multiple facts. Are you
saying the ISBN is a violation of 1NF? This is the first time I've
heard this suggested but this could be interesting. Could you expand
your thoughts, please?

Jamie.

--

Jamie Collins wrote:
Jeff Boyce wrote:
Periodically the subject of "intelligent keys" shows up here in the
tablesdbdesign newsgroup. The consensus has seemed to me to argue
against combining multiple facts into a single field

Is this the same consensus that recommends autonumber primary keys, by
any chance g?

The underlying notion is "one fact,
one field".

I've not seen this soundbite before. My google search:


http://groups.google.com/groups?q=%2....*&qt_s=Search

turned up nothing. Could you clarify your point, please?

That an ISBN "contains many facts" doesn't, in itself, qualify it as
a good example of a key, does it? In fact, I don't know if an ISBN
is a single field (then, by 1NF standards, it would not be a good
design), or is itself a concatenation of multiple, single-fact
fields...

I'm not sure what you are getting at here. I know that the ISBN is an
excellent example of a key and that it contains multiple facts. Are
you saying the ISBN is a violation of 1NF? This is the first time I've
heard this suggested but this could be interesting. Could you expand
your thoughts, please?

You're confusing how the data is stored with how it is "consumed". The fact
that ISBN numbers are published as a monolithic value containing multiple
pieces of information doesn't mean that this would be the proper way to
store them in a relational database. In fact it would not be.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Rick Brandt wrote:
You're confusing how the data is stored with how it is "consumed". The fact
that ISBN numbers are published as a monolithic value containing multiple
pieces of information doesn't mean that this would be the proper way to
store them in a relational database. In fact it would not be.

So are you saying that in your hypothetical 'books' DBMS you would
parse out the elements of the ISBN and persist them in separate
columns?

Jamie.

--

Jamie Collins wrote:
You're confusing how the data is stored with how it is "consumed". The fact
that ISBN numbers are published as a monolithic value containing multiple
pieces of information doesn't mean that this would be the proper way to
store them in a relational database. In fact it would not be.

So are you saying that in your hypothetical 'books' DBMS you would
parse out the elements of the ISBN and persist them in separate
columns?

.... which would end up being split across multiple tables: RegionCodes,
PublisherCodes, etc. Then you'd have to do multiple JOINs and
concatenate the data, just to be able to apply the ISBN's check digit?

Jamie Collins wrote:
Rick Brandt wrote:
You're confusing how the data is stored with how it is "consumed".
The fact that ISBN numbers are published as a monolithic value
containing multiple pieces of information doesn't mean that this
would be the proper way to store them in a relational database. In
fact it would not be.

So are you saying that in your hypothetical 'books' DBMS you would
parse out the elements of the ISBN and persist them in separate
columns?

Jamie.

If the way those bits are combined back together and the data they represent
is consistent I would yes.

Let me qualify that statement by saying that I would do this if I were
designing a database for the entity that "owns" the creation of ISBN
numbers. If I were designing a database for a library (an entity that
merely consumes ISBNs) then I would put it into a single field.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com