My task that I am trying to accomplish should be simple since a DOS program does something similar. Here is the need.
I have a table which has individual identifiers which will not allow duplicates. Lets say these items are
A
AA
AAA
AAA-1
B
BB
BBB
BBB-1 and so on. But there will end up being several copies of any or all of these items which will end up being assigned to different individuals. Like:
Paul has AA #1, AAA-1 #4, and BBB-15 #27
Diane has A #1, AAA #16, CCC-35 #6
Jack has BBB #35
Terry has BBB #36, AAA-1#17
I want to be able to assign a unit with its unique ID (AAA-1) with a sub ID so I can tell if I get the right one back.
The trick to this is, this is a system which will update and replace an old DOS system so there are already items which have their Sub ID if you will already issued out. My table already has fields for the ID (AAA) and a sub ID field (#12). There will be instances where there is already number 327 issued out of AA so I need it to start it's next assigned increment at 328. But others which have no SubIds in use already so it will start with 1. I will know when I start to implement what the highest SubID number is for any specific ID that may be issued. So I need to be able for the system to keep track of what is the next SubID to assign. For example if I use a form which I enter the unique ID into a box and ask to generate the next SubID for that. If I don't put anything in the current SubID box it will generate assignment of #1. But if I put in say 48 in the current highest SubID assigned and ask it to generate it would return the result of 49. Then the next time I ask for the next SubID for that particular item, it would simply tell me to assign it 50.
Is that a understandable explaination of what I'm trying to accomplish? I would appreciate it if anyone could lend their expertise with solving my task.
Thank you,
Kevin