Sequential record numbering within "parent" group
Posted on 2004-09-20
I'm setting up a hierarchy of three related datasets. The governing entity is a PROJECT. A PROJECT may in turn contain multiple ZONES, and each ZONE may in turn contain multiple OBJECTS.
I'm experiencing a brain cramp. Typically I would let Access assign unique ID numbers (autonumbering) for each new ZONE that is created within a PROJECT, but in this case, it will be important for the users to have a "handle" for each ZONE which they can use in referencing any ZONE. This kind of easily identifiable "handle" would not really exist (unless of course the user was asked to assign the ID for each new ZONE), because with autonumbering, the assignment of new ZONES won't have any discernible pattern: the first ZONE for any PROJECT may be assigned ID #345, the second may be assigned #962, and so on.
Maybe I'm just worrying about aesthetics, but it would be nice to have the first ZONE assigned to any PROJECT receive ID #1, the second #2, and so on. So the "Big River" PROJECT has ZONES 1 - 15, the "Little River" PROJECT has ZONES 1 - 9, and so on. My gut feeling is that the only way to gain this sort of autonumbering would be to set the ZONE ID field as LONG, automatically create ZONE 1 when the PROJECT is created, and then fire off a manual routine each time a new ZONE is created which would determine the MAX number that has already been assigned within that PROJECT and just increment it by 1. Index the ZONE table on both PROJECT ID and ZONE ID. Then repeat the same process in the OBJECT table, indexing that table on PROJECT ID, ZONE ID and OBJECT ID.
Do I think aright? Or am I forgetting an obvious element of functionality that Access provides that would handle this kind of repetitive numbering within "Parent" groupings?
Thanks, folks. I never cease to be amazed at the breadth and depth of talent out there, and I look forward to your feedback.