numbering heirarchy + concatenation

[CommonSenseBoy]

i have a heirarchy of tables like this

clients

projects for clients

each table has at least one field which will become a serial number for that
level, and will be incorporated in child serial number like this

clientID "X"
projectID "XY"= clientID "X" & projectCode "Y"
drawingID "XYZ" = projectID "XY" & drawingCode "Z"
....and so on.

i've tried creating this field in the tables themselves (access frowns on
self-referential lookups, i guess); using queries both standalone and in the
lookup fields of the child tables--which breaks relationships becuase there
ends up being no field in a parent table to relate to the child (since the ID
in use is created in a query). it also causes type mismatch errors. i could
always include every field from all parent tables but it seems so inelegant
and cumbersome. what should i do?


thanks, bill

 

[David Cox]

I may be missing the point, but if you run a query linking all of the tables
in the hierachy can you not just generate a calculated field concatenating
the keys from each level?