Preface Autonumber

[theKruser]

I am trying to build a table that's primary key is an autonumber, but want to add "SCHL" to the beginning for ease of import/employment in other tables. Trying to get each new record's PK to be: SCHL1, SCHL2, SCHL3, ETC...

I could have an autonumber field then in the next field have:

SCHLID: "SCHL" & [ID]

However, this seems against normilization rules and a waste of fields. Any ideas? Thanks for your time and help.

 

[KenHigg]

Can you simply concatenate them on the fly as needed?

 

[theKruser]

I am not even sure what that means, but here is my scenario:

I have several "blocks" of training required by different personnel which I have split into seperate tables. The vast majority of the blocks are finite, so I hand-jammed the PK for them. However, I have a few tables that will be ever growing as the need for new training emerges, thus needing an autonumber as the PK.

Here is the twist. All of the training tables' PKs are used in the data table. The data table has the following fields:

TRAINING_DATA_ID: autonumber
EMPL_NO: foreign key
TRAINING_ID: foreign key
DATE: completion date

TRAINING_ID FK comes from each of the training tables. Each of the FKs are prefaced with a table identifier for ease of use in queries, thus the problem. Thanks for the help.

 

[theKruser]

Answer

Oh, yeah...I actually solved my own problem!!

For those who might read this in the future, it is simple. Create your field as type: Autonumber. In the Format box, enter:

"PREFACE"0000

Where PREFACE is the string you want entered before the autonumber. It is important to include the quotes (") before and after the string. The number of zeros (0) will indicate how many digits you want in the number portion. The above example will yeild:

PREFACE0001
PREFACE0002
PREFACE0003
and so on...

Hope this helps someone in the future.

 

[neileg]

Be aware that an autonumber does not guarantee consecutive numbers, only that they are unique. It's not unknown for autonumbers to run 1, 2, 3, 6, 32548, 32549. Using an autonumber for a field that will have meaning to a user is not usually a good idea. Using a format that may obscure part of the autonumber (by just using 4 digits) compounds this.

 

[georgedwilkinson]

And smart keys in general are a bad idea. Companies spend bazillions of dollars (just in meeting costs) dealing with the problems associated with them.

 

[KenHigg]

And smart keys in general are a bad idea. Companies spend bazillions of dollars (just in meeting costs) dealing with the problems associated with them.

I thought the brazillions used pesos - ?