Unique values based on Parent record?

[Wickerman]

I hope this is an easy one. Let's say I have a three-level table structure describing a collection in which each item belongs to a single series which is in turn part of a larger Category, and I want to give each record a unique "Collection #" (not used for key purposes), where the numbers are built out based on the parent. For instrance:

Category # 105

Series # 105.003

Item # 105.003.022

I want each # to be unique within its table -- easy for the Category # - - - but how do I best enforce uniqueness in the Series and Item level? What I *want* to do is build up the numbers based on the parent records so that a user can't violate the rules of parent-child numbering. So, for instance, I don't just want to make the Series # a 6-figure textbox requiring unique value, because I don't want a user to be able to enter, for instance, "109.005" as the child record of a Category whose # is "105" -- I want the parent # to be imposed on the child record - like a prefix almost.

I see that I can make a separate # field at each level and use a concatenation field to build the full number (something like Cat# & "." & Ser# & "." & Item# ) -- but then how do I require that this concatenation be unique? Because I can't just make the 3-digit Item# unique, since many Series and Item #'s will be repeated, as in

105.002.001

105.002.999

105.003.001

Sheesh -- I think this will be easier to solve than it is to describe - I hope so!

Albert

[comment]

The simplest way to do this is NOT to repeat the series and item numbers. Otherwise you need to deal with issues like what happens when a record is deleted, or moved to another group.