I frequently run across a situation in which I need to sequentially
number a subset of records. I have done this several different ways
over the year, but I wanted to see if there is a "right" way.

For example, let's say I have a "jobs" table and a "proofs" table. A
job can have many proofs. Proofs are numbers 1, 2, 3, etc. Simply
serializing the number won't work, because that doesn't "reset" for
each job. In the past, I have scripted it ("Am I the first record in
this relationship? If not, what was the last one? OK, now add 1 to
that."). I also tried to do a calculation to the same effect (I might
have had to concatenate a few values and then pull the right-most
number, or something like that). Both of those seem kind of dirty. Is
there a more elegant way?

In article
<8a0f2829-1163-4bbc-9e81-47e642d870a0 (AT) g27g2000yqa (DOT) googlegroups.com>,
"Joseph O'Brien" <obrien1984 (AT) hotmail (DOT) com> wrote:
The "usual" method is to use a Relationship and the Max function.

The Proofs records must have an ID field linking them to the original Jobs
record, so what you can do is re-use that:

- Create a Relationship in the Proofs Table to group
together all the Proofs from the same Job
e.g.
rel_SameJob
Match records in Proofs Table to records in Proofs Table
when JobID = JobID

- Create (or change) a ProofsSerial Field using an
Auto-Enter by Calculation option which finds the
maximum existing ProofSerial records for the Job
and increments it by 1.
e.g.
ProofsSerial Number, Auto-enter by Calculation
= Max(rel_SameJob::JobID) + 1


Done. )

If you're concatenating together various data to make the serial number,
then use a second Calculation Field to generate that result.
e.g.
ProofSerialFull Calculation, Text Result
= JobID & "-" & ProofsSerial


One possible problem (which exists with any auto-entered serial number) is
when a record is deleted in the middle - you will end up with
non-sequential serial numbers (e.g. 1, 2,4, 5, 10), but that usually
doesn't matter. If it does, then you'll need to Script the re-numbering.

Helpful Harry )