Hi All,

I need to create an auto-incrementing number for a series of records in a child table. It's not a PK just a unique identifier for a record.

I have a parent table called 'Project' which can have 0 or many child records in a table called 'Job'. Each child job record (for a project) would start with 1 and increment up as each record is added. The number is only unique in the domain of a single project. Looks something like this.

Project 1
   Job 1
   Job 2
   Job 3
Project 2
   Job 1
   Job 2
   Job 3

I was considering using a table and storing the last used number (similiar to the new_id function) but I would have to keep a record for each Project.

I'm just wondering if there is a better way to do this.

TIA