are you allocating numbers centrally of locally.. is it one db, or does each site / each building have its own db
if 'all' you want is a unique number then 'just' use an autogenerated number
on reports and elsewhere prefix that autogenerated number with the site / building / wahtever data element
the drawback of this approach is that you will not have contiguous numbers for eachs site / building.
Im not sure its a problem.. but it may be to your user base if they want to see
as opposed to what Id go for which could be...
in reality you would have
EmpId an autogen filed PK
SiteID say text (2)
BuildingID say text (2)
a compound index SiteID | BuildID | EmpID
you would need to put a bit of code (probably in a function in a common module available to all forms, reports & queries) behind all search functions to strip off the redundant alpha characters. you might need another fuicntion to explode the 9 digit empref into its 3 components... might be better to do that as a class with 3 methods. Effectively your app looks for the EmpID to find a vlaue, whereas the users will see it as the compound value. you have to do a bit more work but they see what they want, you use an auotincrement to allocate the ID.
If you must have contiguous numbers for each site/buildiong then alternative is to find the current highest number for each site/building as required. That will require table locks, and a lot more design work.
There are going to be purists who will say that you should never allow such an abomination, you shouldn't use alphas in primary keys, but if thats what the customer wants......
If your users 'insists' on contiguous numbers then Id check why they want that.. after all if they are temporary employees then by defintion they are transient, if they need contiguity get them to justify, if they want to know who is on site to day.. then keep an issue number, or generate one on the day (eg COUNT for a specific site/building element)
I'd rather be riding on the Tiger 800 or the Norton