Unique ID's for Document Control

[LianeMT]

I'm working on an Access db that tracks documents from initial creation
through a sign-off and release process. Each document needs to be assigned a
unique identifier of a specific format (out of my control) and there cannot
be duplicates. Ultimately there may be 1000s of documents in the system. Here
is the alphanumeric format I'm stuck using: XXXYYZZZZRR – WWW

XXX is actually a fixed assigned number (Ex: 301)
YY is a designator that may be 1 or 2 characters (Ex: "A" would indicate a
model, "AA" would indicate a drawing, "D" a document, "TR" is training
manual, etc)
ZZZZ is the semi-unique numeric identifier (Ex: 2001, or 4432, etc)
RR is the revision/version ID and starts with A and goes through ZZ
-WWW is a numeric tag indicating sequence for a specific drawing or assembly
(Ex: -001, -005, etc)

Sample Document Numbers:

301D2004F-001 --> A stand-alone document (Rev F) for the 2004 installation
301TR3353C-000 --> A training procedure (rev C)
301A2122BB-000 --> The 2122 Model (Rev BB)
301AA2122BB-002 --> Drawing (Rev BB) for part -002 of the 2122 model
301AA2122BB-003 --> Drawing (Rev BB) for part -003 of the 2122 model

I am trying to figure out an efficient method to set up the document ID that
will allow them to be sorted by the "YY" type and automatically incremented
by the "ZZZZ" portion (or ZZZZ-WWW if necessary) so that when I assign the
next document number, I don't inadvertantly repeat them.

If any of you MVPs or other Access experts out there has some sage advice to
offer, I'd greatly appreciate the help/insight!

Thanks, much!

 

[Jeff Boyce]

Liane

First, I'd create as many fields as I had different pieces of data. It
sounds like your "ID" has 5 separate pieces of information.

Then I'd add a new index to the table that spanned all five fields, and make
it a Unique index (no duplicates).

Finally, I'd create a query that concatenates them together in the "for
display" format you gave ("XXXYYZZZZRR-WWW").

Just because someone calls it an "ID" doesn't mean you have to make it a
single ID field in an Access table. After all, you aren't going to let them
work directly in the tables anyway, right?! (that's what forms are for)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[LianeMT]

Jeff,

That makes perfect sense to me! I had begun setting up the separate fields
as you suggested before posting my question. My "knowledge gap" came in how
to get the pieces together. I'll take a swing at the 5-field spanning index
and the query next.

And no, the user base will certainly not be working directly in the tables!
What a disaster that would be.

Thanks for the advice!
Liane

 

[Jeff Boyce]

You're welcome

Jeff

Jeff,

That makes perfect sense to me! I had begun setting up the separate fields
as you suggested before posting my question. My "knowledge gap" came in
how
to get the pieces together. I'll take a swing at the 5-field spanning
index
and the query next.

And no, the user base will certainly not be working directly in the
tables!
What a disaster that would be.

Thanks for the advice!
Liane