This can be done, but first explain why it is necessary and whether two or more users might need to add records at the same time or not. If you can get your solution to work with a single series of auto-entered serial numbers, your database is much easier to maintain and work with.
Also, it can make a difference whether your groups are static or dynamic here. Will you only have just "Potatoes" and "Tomatoes" as your two series of numbers or could the user add a new groups (say Lettuce) at any time?
Thanks for helping me out!
I might use only a single serial number for each record, that’s true, but in this file I work with documents that are printed, and it seems practical to have the initial of the item integrated in the serial number for better recognition. There is only one user (that’s me) who will add new records.
The file is an educational programme with eight different items, and in each of them an infinite number of records. Making a new record, this one should recognize its item and produce a serial number next to the last one of the same item.
The groups are static, although slightly possible in the future a new group might be added.
It seems practical to have the initial of the item integrated in the serial number for better recognition.
You can include the initial without specifying different number series for each and that would avoid the need for the special handling of serial numbers.
What does each record in your table represent? It sounds like you need two tables, one with a record for each document and one with a record for each time it is printed or each time it is revised or some such...
That table structure can make managing the number series for each document a bit easier...