If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Unique ID's for Document Control
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" wrote in message ... 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! |
#3
|
|||
|
|||
Unique ID's for Document Control
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" wrote: 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" wrote in message ... 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! |
#4
|
|||
|
|||
Unique ID's for Document Control
You're welcome
Jeff "LianeMT" wrote in message news 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" wrote: 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" wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|