Auto-Numbering using two fields

[Leslie]

I'm in the design phase of a DB project, and want to create auto-numbered
keyfields, but I want each "primary" table to have its own "prefix" to the
auto-numbered field. E.g. Vendor would be VN as the "prefix" and then an
auto-numbered numeric field as the "auto-number" field. I would then
concatenat the two to make the field like VN00001, VN00002, etc. I would do
this for each "primary" table in the DB.

Does anyone have input or BKMs (Best Known Methods) for doing this kind of
thing?

Thanks,
Leslie

 

[Rick Brandt]

I'm in the design phase of a DB project, and want to create
auto-numbered keyfields, but I want each "primary" table to have its
own "prefix" to the auto-numbered field. E.g. Vendor would be VN as
the "prefix" and then an auto-numbered numeric field as the
"auto-number" field. I would then concatenat the two to make the
field like VN00001, VN00002, etc. I would do this for each "primary"
table in the DB.

Does anyone have input or BKMs (Best Known Methods) for doing this
kind of thing?

No need to store the prefix. Just use a format property to *display* the
"VN" like this...

=Format(PKField, "VN00000")

 

[Leslie]

Rick,

Thank you for your response. I would actually prefer that the "prefix" get
stored in the keyfield, as I want to build reports based on this, and there
is the potential to have multiple tables linked and I'm afraid without the
prefixes, the lookups could get very confusing and messy.

How would you deal with this on the "other" side of the world - the
extracting of the data?

Leslie

 

[Justin]

I don't believe you can change the auto-numbering format. I would add an
additional field to place the prefix in. You can then combine them for
display. I also would put all data into one table (vendor, customers, etc)
and sort off of this field.

 

[Marshall Barton]

I'm in the design phase of a DB project, and want to create auto-numbered
keyfields, but I want each "primary" table to have its own "prefix" to the
auto-numbered field. E.g. Vendor would be VN as the "prefix" and then an
auto-numbered numeric field as the "auto-number" field. I would then
concatenat the two to make the field like VN00001, VN00002, etc. I would do
this for each "primary" table in the DB.

Does anyone have input or BKMs (Best Known Methods) for doing this kind of
thing?

You should never combine two or more meaningful pieces of
data in a single field.

If you must do this, and I can't see a good reason for it,
then use two fields, one for the AutoNumber value and
another, completely redundant, field for the prefix.

Please note that an Access created AutoNumber primary key
field is not guaranteed to be anything beyond unique (there
will be gaps in the sequence, the value can be negative, the
sequence may become random, and, if you should ever decide
to use Replication, will not even be a number). For any of
these reasons, an AutoNumber should never be exposed to
users, because they will inevitably attempt to assign it
some meaning that you can not guatantee. Following that
line of reasoning, if users never see the AutoNumber value,
there is absolutely no purpose served by adding a table
identifying prefix to the number.

 

[Leslie]

Justin,

Thank you for your response. Are you saying to store all of the "Key
Numbers" in a table just for Key Numbers? I'm not sure I follow completely.
My apologies. Can you clarify for me a bit more.

Leslie

 

[Justin]

Lesile,

I wouldn't have seperate tables for vendors, customers, employees, etc. I
would store then all in one table with the auto-number key field. I would
have a second field to store the "Type" information (vendor, customer, etc).
You can sort/query with this field. This will simplify your system design by
elimenating multiple tables and key fields.

 

[Leslie]

Marshall,

Thank you, you have provided "sage" advice. I do not plan to let the users
update the "key" field, but I would display it and allow them to search on
it. I do want the key field to have the "prefix", and I think I could
possibly have a table of key-field "prefixes" and do a look up on that, then
concatenate with the auto-numbered field... Or maybe I simply store a "last
used" number in a separate table, do a calculation to use that number and
"auto-increment" the "last used" number once I've assigned the new "unique"
number. I'm not so concerned about gaps in sequencing, as long as the
numbers are unique (tho' I wouldn't want a negative number).

As you can see, I'm still trying to "figure it out"... Your input is
appreciated.

Cheers,
Leslie