General solution for missing sequence numbers
To summarize, I believe that the general question is - What is happening
that is causing the gaps to occurr?
John Vinson wrote:
On Mon, 5 Dec 2005 18:53:45 +0100, "Peter Danes"
wrote:
Such situations are common, for a variety of reasons. Depends on the
database and the user and what the data is for. The particular example that
inspired this outburst is a mycological database, where the numbers are used
to sequentially number the scientist's samples. She told me that numbering
is important for others in the field to know roughly how many samples a
particular researcher has, and for internal inventory purposes, that they
don't expect to have holes in the numbering sequence.
I agree that Access' autonumber isn't suitable in such cases... but
there are real, major problems with "filling in the gaps."
How did the gap get there in the first place? Presumably a Sample #312
was entered at some point, and then deleted: the entry was found to be
erroneous, misnumbered, or for some other reason had to be removed.
New entries would go in up at Sample #844 but you now have this gap.
OK... fill in the gap then. BUT!
What if there's a publication referring to the (erroneous) old Sample
312, and you now assign a DIFFERENT sample the same number? What if
someone has 312 written down on a Post-It note as "check up on this
really interesting sample" - or noted in their memory? Sure, you can
change it in the database; but where *else* does the information
exist, and can you change *that*?
John W. Vinson[MVP]
|