Autogenerating Numbers for a primary key field, "studyId," in a tablebut with a few

I have a question on autogenerating numbers for a primary key field, "studyID," in a table—but with a few twists.

We want studyID to be automatically generated as a 5-digit number. Additionally, we have two study sites and would like the studyIDs pertaining to the first site to begin with a 1 and StudyIDs associated with our second site to start with a 2. When we begin entering data, we will enter either a 1 or 2 in a field called, "Site." Upon entering that 1 or 2, we would like at that moment for Access to instantly autogenerate the appropriate studyID for that site and put it in the "StudyID" field. We want the very first number generated for each site to end in a 1 (10001 and 20001).

Here’s the range of values we want our StudyIDs to be (this is to be our validation rule as well):

10001-19999 for Site 1

20001-29999 for Site 2

Your suggestions are VERY VERY WELCOME! THANKS!

If all sites were in seprate databases or at least tables it would be easy just set "identity increment" =1 and "identity seed" = [side prefix]0001 during table creation, but you need maintain all of this in one table so probably trigger is the only solution.

Tomek

|||

This is the fundamental problem with autoincrementing pk fields. You can't really do this in one table. You could create 2 tables and use the post above, then join them into 1 table for output; but, realistically you can't accomplish this goal in 1 table with 1 autogenerated number with arbitrary insert order and more than one logical grouping of keyspaces.

It is generally suggested that if you can possibly avoid it, you should not use autoincrementing pk fields -- but rather use primary keys or clustered keys based on the content of the data itself if you can guarantee uniqueness. If you cannot, it may be advantageous in the long run to avoid duplicate rows by using a count in your table.

e.g. First Last

John Gordon

John Gordon

becomes

First Last Count

John Gordon 2

In the short run, you could split your table into two and join them to report, but in the long run, you may want to consider the limitations this design imposes on the index space of your data and the possibility for growth.

Hope that helps,

John