Wm Allen Smith
asked on
Auto-generated ID's in MS Access
Good Day,
Currently I have an excel spreadsheet that tracks business requirements. The sheet consists of four columns: Id (varchar), category (varchar), description ( longtext), and process (int). I want to convert the data entry process from spreadsheet entry to a form entry using MS Access for better error handling.
My challenge is creating a method in Access where the id, formatted as X##-XXX-XX##, can be auto generated for each requirement row based on the requirement type.
For instance, if the the business requirement is for banana, the id should be Y17-BAN-BR01 and increment for each banana requirement; for apple: Y17-APP-BR01, and so on. The id's only need to be sequential for the same requirement type (BAN for banana, APP for apple, ORA for orange etc.) and there can be no duplicate id within each requirement type. The user will need to enter the requirements in random order as they come in. I know that autonumber will not work for this.
What 's the best way to achieve this? Individual tables/queries for each type? I do not want to have multiple forms. Please advise.
Thanks,
wasmithpfs
Currently I have an excel spreadsheet that tracks business requirements. The sheet consists of four columns: Id (varchar), category (varchar), description ( longtext), and process (int). I want to convert the data entry process from spreadsheet entry to a form entry using MS Access for better error handling.
My challenge is creating a method in Access where the id, formatted as X##-XXX-XX##, can be auto generated for each requirement row based on the requirement type.
For instance, if the the business requirement is for banana, the id should be Y17-BAN-BR01 and increment for each banana requirement; for apple: Y17-APP-BR01, and so on. The id's only need to be sequential for the same requirement type (BAN for banana, APP for apple, ORA for orange etc.) and there can be no duplicate id within each requirement type. The user will need to enter the requirements in random order as they come in. I know that autonumber will not work for this.
What 's the best way to achieve this? Individual tables/queries for each type? I do not want to have multiple forms. Please advise.
Thanks,
wasmithpfs
A second to Ander's comment, but from a relational perspective. You never want to embed multiple pieces of information in a single field.
Instead, you would have separate columns for each part of the requirement type. And following modern practices, you'd assign an autonumber field as the primary key, and have the additional columns compounded to from a candidate key.
Building meaning into a single key field is always a bad idea.
Jim.
Instead, you would have separate columns for each part of the requirement type. And following modern practices, you'd assign an autonumber field as the primary key, and have the additional columns compounded to from a candidate key.
Building meaning into a single key field is always a bad idea.
Jim.
ASKER
Therein lies the problem.
The id format is already used comapny-wide and has passed compliance, so I cannot change the format. I would actually prefer to make it a composite key based on record index and requirement type field ( 101-BAN). But again, within each type, the index must be sequential due to requirements and testing, so 101-BAN may be the first requirement for banana, 06- APP might be the fourth requirement for apple.
Thoughts?
wasmithpfs
The id format is already used comapny-wide and has passed compliance, so I cannot change the format. I would actually prefer to make it a composite key based on record index and requirement type field ( 101-BAN). But again, within each type, the index must be sequential due to requirements and testing, so 101-BAN may be the first requirement for banana, 06- APP might be the fourth requirement for apple.
Thoughts?
wasmithpfs
Continuing Anders' and Jim's excellent advice. You can still generate the intelligent ID based on the specs you've provided and you can use it for searching and display. You just shouldn't use it as the primary key or foreign key - EVER. As long as you store the parts of the unique identifier in separate fields, you will retain a certain amount of flexibility. You can concatenate the parts for display so the user won't even know. Just be consistent with your separators and you won't have any trouble working with the intelligent ID. And, more importantly, you won't have to say NO or that will simply be too expensive when the client changes the business rules on you since the autonumber behind the scenes will be your glue. You could store the parts mushed into a single field provided your separators are consistent but I don't recommend it since you will never be able to change the display format if you do that because you have to parse the field to get to the part you need to increment.
Keep in mind that even sequence numbers assigned this way can have gaps. If you delete a record, you should never attempt to fill the gap and you will need to include looping code when generating the number in case someone else is attempting to add a new ID at the same time. If you get a key violation, increment the sequence number and attempt the save again.
Keep in mind that even sequence numbers assigned this way can have gaps. If you delete a record, you should never attempt to fill the gap and you will need to include looping code when generating the number in case someone else is attempting to add a new ID at the same time. If you get a key violation, increment the sequence number and attempt the save again.
ASKER
Thanks to everyone for their input.
The id column in my system is not intended to be the primary key--the primary will be a hidden system generated key. Access only allows one autonumber field in a table.
Let me re-state the challenge: creating an additional field that auto increments so that those belonging to a particular requirement type have their own sequence (BAN 1 through N, APP, 3 through 12, ORA 1 to 14, etc) which can then be concatenated to form the requirement ID. Does this mean creating separate tables for each type or separate queries upon which a form could be used to enter the data?
The id column in my system is not intended to be the primary key--the primary will be a hidden system generated key. Access only allows one autonumber field in a table.
Let me re-state the challenge: creating an additional field that auto increments so that those belonging to a particular requirement type have their own sequence (BAN 1 through N, APP, 3 through 12, ORA 1 to 14, etc) which can then be concatenated to form the requirement ID. Does this mean creating separate tables for each type or separate queries upon which a form could be used to enter the data?
<< As long as you store the parts of the unique identifier in separate fields, you will retain a certain amount of flexibility. >>
This is the critical point. For example, if BR01 is the ID in this "Y17-Ban-BR01", what happens when you get to BR99 and need another?
Seems like your already aware of those pitfalls, but as Pat said, display/searching is one thing, internally how you handle it is another.
You'll need to use a key generation procedure no matter what. There's no way to handle what you want that's built into Access. That would take the form of a table, and a procedure that would:
1. Lock the key table.
2. Lookup the business requirement.
3. Grab the last value and increment by 1
4. Save the new value.
5. Return the key.
You want to do this for two reasons:
1. If you try to determine the MAX() value for the increment, in a multi-user situation, two users may end up with the same key.
2. You don't want to lock the main table itself for concurrency reasons.
an alternative to this is to simply use MAX(), try to add, and see if it fails. If it does, increment by one and try again. But I like the key table approach myself.
Jim.
This is the critical point. For example, if BR01 is the ID in this "Y17-Ban-BR01", what happens when you get to BR99 and need another?
Seems like your already aware of those pitfalls, but as Pat said, display/searching is one thing, internally how you handle it is another.
You'll need to use a key generation procedure no matter what. There's no way to handle what you want that's built into Access. That would take the form of a table, and a procedure that would:
1. Lock the key table.
2. Lookup the business requirement.
3. Grab the last value and increment by 1
4. Save the new value.
5. Return the key.
You want to do this for two reasons:
1. If you try to determine the MAX() value for the increment, in a multi-user situation, two users may end up with the same key.
2. You don't want to lock the main table itself for concurrency reasons.
an alternative to this is to simply use MAX(), try to add, and see if it fails. If it does, increment by one and try again. But I like the key table approach myself.
Jim.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your assistance.
Welcome!
My strong recommendation is to have req ID be just that, a simple unique ID, with no meaning what so ever.