Unique ID's for Document Control

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!

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" <LianeMT.DeleteThis@discussions.microsoft.com> wrote in message
news:63714D51-D19D-4103-9B64-971B563DD35C@microsoft.com...
> 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!