Why do you want to do that in the first place? (In database terms, it's not a good idea and there are likely better alternatives)
Sometimes you have to do this kind of thing because a client insists on it or you have to support either a special labeling system or a legacy ID system that predates your solution, but I don't advise this ID method if you can possibly avoid doing so as getting correct, consistent values--especially in a multi-user solution, can complicate your solution.
1 of 1 people found this helpful
I would make these separate fields/variables and recombine for reports/printing as needed.
Sent from miPhone
To expand on how different serial numbers for different categories can complicate your solution, the typical methods use one method or another, ExecuteSQL, A sorted relationship, the Max function with an unsorted relationship... to get current max value for a given category in order to add one to that value to get the next value in sequence.
Where this typically fails is when two users try to add a new record in the same category at the same time. They can both get same max value and thus the same next value. You thus have to test for and handle duplicates should they happen.
I know of a method that does not have this limitation, but it requires a different dedicated table for each category. This makes it impractical if the number of categories is not fixed.