Can I have an Identity Column based on the Primary Key instead of the table

mburk1968 used Ask the Experts™
I have a table where the primary key is the Claim Number.  If I have more than 1 Claimant for a Claim Number, I would like the identity column (Claimant Number) to increment by 1.  Therefore, for Claim Number 12345, I would have 2 records where the Claimant Number would equal 1 and 2 respectively.  However, now I have Claim Number 67890, and again I have more than 1 Claimant.  I don't want the Claimant Numbers to be 3 and 4.  I want them to be 1 and 2 again since this is a new Claim Number.  Can this be done using an Identity Column, and if not, does anyone know of an easy way to accomplish this? Thanks...
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT Engineer
Distinguished Expert 2017
As far as I know you can't do it with Identity columns. One of the rules is that you can have only one Identity column by table, so you need to do it manually (function?).

Good luck

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial