>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.

Since it's being done so that you autoincrement within the Project, how about adding an nNextJobID field to the project record, setting it to 1 when you first create the Project, and when adding a job for the project, using that as the basis for the number, incrementing the nNextJobID each time you add a new job to the project?