AlphaNumeric IDs

[DbMstr]

I like to use AlphaNumeric(AN) IDs, (0-9 and A-Z) in a FIXED field
length especially if they will ever be viewed as concatenated with
another field. For example, BF3D.

If there is human intervention anywhere I usually throw out the
characters that are easily confused when viewed in some fonts or
handwritten. For example, l10OQZ26GVU and such.

One of the benefits of this approach is that a simple 4 character AN ID
can uniquely identify the following numbers of records:
9,999 numbers, i.e., 0937
456,976 AN UpperCase with 10 error prone characters removed, i.e., HAA3
1,679,616 AN including all UpperCase characters, i.e., 10OV
14,776,336 records using all AN characters both upper and lower case,
i.e., 3eHo

I choose the number of characters I need based on a clients project
needs. A year or region field can be easily combined to create a
primary key and you could start over each year if you like.

I have a routine I've been using for years with a table with acceptable
characters for a project so I can define whether to use all AN
characters including upper and lower case or just some and in any order
I choose.

I have another table with a single ID record that might begin with AAAA
or 0000 and code that increments the ID by looking at the last entry
and changing it based on my lookup table. This is not real fast when
generating a large number of these IDs but normally I just need one at
a time for a new unique record on an add new record form.

I have the added benefit of using this ID elsewhere such as in all or a
portion of file naming and then easily tying everything together or
finding some of a large number of files at any time.

I previously developed in a relational database language that had this
feature built in but I find no easier way for Access to accomplish
this. I shy away from the Autonumber as much as possible as a personal
preference.

Has anyone ever seen, developed or used a better method to create
defined AN IDs that I could use than my two table process?

 

[John Vinson]

Has anyone ever seen, developed or used a better method to create
defined AN IDs that I could use than my two table process?

If you want a 4-byte unique arbitrary ID, with over 4 billion possible
values, why not use a Long Integer? 3112814 is no less meaningful than
3AQ7, and takes up half the space (thanks to Unicode your four
characters will occupy eight bytes). And it's *easy* and fast to
assign.
John W. Vinson[MVP]

 

[DbMstr]

If you want a 4-byte unique arbitrary ID, with over 4 billion possible
values, why not use a Long Integer? 3112814 is no less meaningful than
3AQ7, and takes up half the space (thanks to Unicode your four
characters will occupy eight bytes). And it's *easy* and fast to
assign.
John W. Vinson[MVP]

I am not suggesting a 4 byte length but rather a fixed character length
such as the 4 character length in my example. I agree with you when
considering 1s and 0s byte length but when working with a fixed visual
and factual length field ID and doing things like using an ID for
additiona purposes such as part of a file naming scheme for example for
naming scanned images for each 'page' that relate to a single document
recordID I have found this process useful. I am not saying everyone
should use or consider my process nor my concept.

My question is, has anyone found a more efficient way to accomplish
what I want to do?
Thanks for any feedback or response.
Dennis

 

[John Vinson]

I am not suggesting a 4 byte length but rather a fixed character length
such as the 4 character length in my example. I agree with you when
considering 1s and 0s byte length but when working with a fixed visual
and factual length field ID and doing things like using an ID for
additiona purposes such as part of a file naming scheme for example for
naming scanned images for each 'page' that relate to a single document
recordID I have found this process useful. I am not saying everyone
should use or consider my process nor my concept.

My question is, has anyone found a more efficient way to accomplish
what I want to do?

Thanks for the explanation, and sorry for being so didactic.

Since you didn't post the details of your code, it's a bit hard to
debug it or suggest how it could be more efficient. How are you
eliminating already-used values? The tack I'd suggest would be an
Unmatched Query:

SELECT TOP 1 IDCodes.IDCode
FROM IDCodes LEFT JOIN targettable
ON IDCodes.IDCode = targettable.IDCode
WHERE targettable.IDcode IS NULL
ORDER BY IDCodes.IDCode;

If IDcode has a unique Index (Primary Key) in each table this should
be very quick.

John W. Vinson[MVP]

 

[DbMstr]

Thanks for the explanation, and sorry for being so didactic.

Since you didn't post the details of your code, it's a bit hard to
debug it or suggest how it could be more efficient. How are you
eliminating already-used values? The tack I'd suggest would be an
Unmatched Query:

SELECT TOP 1 IDCodes.IDCode
FROM IDCodes LEFT JOIN targettable
ON IDCodes.IDCode = targettable.IDCode
WHERE targettable.IDcode IS NULL
ORDER BY IDCodes.IDCode;

If IDcode has a unique Index (Primary Key) in each table this should
be very quick.

John W. Vinson[MVP]

Thanks John,
What I was looking for was a general response to my question of methods
for creating AlphaNumeric IDs as opposed to an Autonumber or concat
fields. I gave some examples why I have chosen to use them and hoped
to find others who have adopted a similar strategy and discover how
they implemented it. Didn't want to get into the merits here.

The method I use is an tblIDChr table with all the possible ID
characters I have chosen for a project, (i.e., 0-9, A-Z and sometimes
a-z), and another table, tblIDGen where I maintain the LAST id created
using a fixed length number of characters (4-6 is fairly common for my
projects). Something like 3XA5 and the next would be 3XA6 for example.

I use form code to cycle the single record in tblIDGen to the next
value and I then apply that value to the ID field of a new record
created through a user form. They are always unique and never reused
and loss is no loss. Each time a new record is created the code looks
to this table and generates a new one and grabs it.

I hoped there might be more efficient ways to create a restriced and
defined AlphaNumericID. I can post the process and the code I use if
anyone is interested but first I wanted to discuss the coding concept
before the details.