autonumber queestion

[Ted]

I have a database for personal use in my woodturning hobby. I want to create
an item number for each item I make. One table has a field ItemNumber (two
letters, ie, PS, PM, PB, WS, etc) Another table will have the two letter
field for WoodType (two letters, ie, BO, CO, MB, etc).

I want to create an autonumber field that will combine ItemNumber and
WoodType and then add the next sequential number for the 4 letter prefix.

For example, PSCO001, PSCO002, PMCO001, PBMF001

 

[John W. Vinson]

I have a database for personal use in my woodturning hobby. I want to create
an item number for each item I make. One table has a field ItemNumber (two
letters, ie, PS, PM, PB, WS, etc) Another table will have the two letter
field for WoodType (two letters, ie, BO, CO, MB, etc).

I want to create an autonumber field that will combine ItemNumber and
WoodType and then add the next sequential number for the 4 letter prefix.

For example, PSCO001, PSCO002, PMCO001, PBMF001

Don't.

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

A basic principle of relational databases is that fields should be "atomic" -
having one and only one piece of information.

The wood and the finish are each independent attributes of the item and should
not be part of its key; just have fields for the wood type and the finish, and
use a (meaningless) sequential autonumber as the primary key. You can
concatenate the three fields for display if you need it.

One thing to consider - a vendor at our local farmers' market makes beautiful
turned wood pieces of several types of wood neatly glued together. That might
not be your style today but you wouldn't want to rule it out just because of a
software design flaw, would you?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Ted]

I can combine the first to parts, it is the autonumber that I am having the
issue with.

The wood type and item are important as if there are two of the same wood
type and item type, then one should have 001 as the suffix and the second
should have 002.

The si

"John W. Vinson" wrote in message

I have a database for personal use in my woodturning hobby. I want to
create
an item number for each item I make. One table has a field ItemNumber (two
letters, ie, PS, PM, PB, WS, etc) Another table will have the two letter
field for WoodType (two letters, ie, BO, CO, MB, etc).

I want to create an autonumber field that will combine ItemNumber and
WoodType and then add the next sequential number for the 4 letter prefix.

For example, PSCO001, PSCO002, PMCO001, PBMF001

Don't.

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

A basic principle of relational databases is that fields should be
"atomic" -
having one and only one piece of information.

The wood and the finish are each independent attributes of the item and
should
not be part of its key; just have fields for the wood type and the finish,
and
use a (meaningless) sequential autonumber as the primary key. You can
concatenate the three fields for display if you need it.

One thing to consider - a vendor at our local farmers' market makes
beautiful
turned wood pieces of several types of wood neatly glued together. That
might
not be your style today but you wouldn't want to rule it out just because of
a
software design flaw, would you?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Access Developer]

If you want an identifying code as you suggest, you'll have to create it
yourself.

"Autonumbers" are specifically intended to provide a unique identification
(sometimes known as a "surrogate key"). Although in their most common form,
they are monotonically increasing numbers, with some exceptions -- you
can't count on not having gaps in the sequence. Autonumbers work in a
specified way, and there's no provision for "custom autonumbers".

Perhaps you did not understand from John that, if you violate relational
rules (by creating an 'intelligent key'), sooner or later, your
not-quite-relational database will be more difficult for you to use.

I'm sure you feel that you *need* that key, but believe me, you can query
your database to retrieve all (or a subset of) items with a specific wood, a
specific finish, and, separately, a count of how many of those). But, I'd
wager that some here can show you how to accomplish the same purpose and
still follow proper relational database design.