I am creating a new database in which we are storing records for three categories - RM, PK and FG. We currently track how many we have of each on an Excel spreadsheet where each entry has the following format for unique identification:
The unique identifier is a combination of: 1) The current year, 2) A sequential number (i.e. you cannot go from 3 to 5, a 4 must exist), and 3) The category (RM, PK or FG).
Also, when a new year starts, the sequential numbering starts again (i.e. 2010-01-FG).
This formatting I cannot change because it is required for audit purposes.
What is the best way to go about this and how do I do it? I imagine using the autonumber property is still a good idea to build relationship between tables and maybe have this identifier as another field that is automatically generated. But, I also know that you can only have one autonumber field per table. I am not very good at coding, so if this is the case, I would appreciate some help.