Assigning consecutive Serial Numbers to non-consecutive records
I've set up a DB for a design studio, in which each job has a docket number.
We have two types of dockets:
1. DUPLICATE dockets (where a docket is worked on multiple times, we want to record EACH instance of the job as separate records, but use the same docket number).
2. NEW dockets, which need to be assigned NEW docket numbers.
Question: What formula/calculation can I use so it skips over duplicate records and assigns the next consecutive serial number from the last NEW job (ie not necessarily the last new record)?
Example of entering records:
New Record: New Docket: Assign next consecutive number: 1001
New Record: Duplicate Docket #985 (keep same docket number)
New Record: New Docket: Assign next consecutive number: 1002 (currently this record gets assigned 1003, because it counts the previous (duplicate) record as 1002, which I want it to ignore).
I hope this makes enough sense that someone can help me. I'm pulling my hair out!! Thanks.
I need a field that will assign a consecutive serial number to a new record, but NOT assign a serial number to a duplicate record.