To make this script work, I was thinking of creating seperate tables- one called "License" and one called "Product," with two seperate fields for the name of the license/product ("Disney") and the abbreviation ("WD"). Is that a good idea?
Yes, that sounds like a good idea.
BUT, do not use this field as a primary key for linking to other tables in your database. Use something simpler and safer such as an auto-entered serial number and leave this ID code as a field in just the product table. That way, you can use the field for searches and sorts, but if the powers that be change the values in this field, it won't screw up the function of your database.
And be very careful how you set up your system for generating the serial number portion of this Identifier field. If it is ever possible that two or more users might be generating new ID codes at the same time, there is a chance that you will get duplicate values. You need a text field that uses an auto-enter calcualtion to combine the three values in to a single code and then you can set up a unique values validation field option to ensure that you cannot get duplicate values in this field.
Thank you for the reply and all the suggestions!
Firstly, I need some help with the calculation. If you could at least point me in the direction of a forum post that previously addressed a similar question and would have an example to help, I'd be grateful.
Also, there will never be more than one user generating new ID codes at the same time, so hopefully that diminshes the probability of duplicate values? However, I AM worried that an auto-enter calculation may overlook the ID codes that are already in the DB and create duplicates. Is there a way to assure that it will generate the code following a value that was in the DB before the calculation was created and implemented? For example, if there was already an ID code "WDWA0145," the next code it generates should be "WDWA0146" and so on. There are already over 12,000 such ID codes in the DB.
I'm not sure if you need to know this in order to help, but I'll post it just in case. Before the ID code is generated, the user will choose the License field and Product field values from a drop-down list whose values will be from a value list that displays data from the fields in their respective tables. For example, the Product Type value list display values from Product::Product Type, License Type from License::License Type, etc.
Thanks again for all of your help!
Also, there will never be more than one user generating new ID codes at the same time,
Make sure that is NEVER EVER. If there is even the slightest possibility that this could happen, you need to take precautions or you can end up with chaos in in your data. The Unique Values validation method I mentioned earlier should do that.
With a self join relationship that matches values by just the first part of this identifier (WDWA in your latest example). Max ( SelfJoin::Serial ) + 1 can be used in an auto-enter calculation to enter the next value in the series. The number field would use an auto-enter calculation such as:
Max ( SelfJoin::Serial ) + 1 with the "do not replace existing value..." box cleared.
The full autoenter calculation looks like:
LicenseCode & ProductCategoryCode & Right ( "000" & Serial ; 4 )